Summing And Counting Using Multiple Criteria

Category: Formulas | [Item URL]

If you peruse the Excel newsgroups, you've probably realized that one of the most common questions involves summing or counting using multiple criteria. If your data is set up as a database table you can use database functions such as DCOUNT or DSUM. These functions, however, require the use of a separate criteria range on your worksheet.

This tip provides a number of examples that should solve most of your counting and summing problems. Unlike DCOUNT and DSUM, these formulas don't require a criteria range.

The example formulas presented in this tip use the simple database table shown below. You will need to adjust the formulas to account for your own data.

Sum of Sales, where Month="Jan"

This is a straightforward use of the SUMIF function (it uses a single criterion):

  =SUMIF(A2:A10,"Jan",C2:C10)

Count of Sales, where Month="Jan"

This is a straightforward use of the COUNTIF function (single criterion):

  =COUNTIF(A2:A10,"Jan")

Sum of Sales, where Month<>"Jan"

Another simple use of SUMIF (single criterion):

  =SUMIF(A2:A10,"<>Jan",C2:C10)

Sum of Sales where Month="Jan" or "Feb"

For multiple OR criteria in the same field, use multiple SUMIF functions:

  =SUMIF(A2:A10,"Jan",C2:C10)+SUMIF(A2:A10,"Feb",C2:C10)

Sum of Sales where Month="Jan" AND Region="North"

For multiple criteria in different fields, the SUMIF function doesn't work. However, you can use an array formula. When you enter this formula, use Ctrl+Shift+Enter:

  =SUM((A2:A10="Jan")*(B2:B10="North")*C2:C10)

Sum of Sales where Month="Jan" AND Region<>"North"

Requires an array formula similar to the previous formula. When you enter this formula, use Ctrl+Shift+Enter:

  =SUM((A2:A10="Jan")*(B2:B10<>"North")*C2:C10)

Count of Sales where Month="Jan" AND Region="North"

For multiple criteria in different fields, the COUNTIF function doesn't work. However, you can use an array formula. When you enter this formula, use Ctrl+Shift+Enter:

  =SUM((A2:A10="Jan")*(B2:B10="North"))

Sum of Sales where Month="Jan" AND Sales>= 200

Requires an array formula similar to the previous example. When you enter this formula, use Ctrl+Shift+Enter:

  =SUM((A2:A10="Jan")*(C2:C10>=200)*(C2:C10))

Sum of Sales between 300 and 400

This also requires an array formula. When you enter this formula, use Ctrl+Shift+Enter:

  =SUM((C2:C10>=300)*(C2:C10<=400)*(C2:C10))

Count of Sales between 300 and 400

This also requires an array formula. When you enter this formula, use Ctrl+Shift+Enter:

  =SUM((C2:C10>=300)*(C2:C10<=400))