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.

http://spreadsheetpage.com/graphics/tips/table.gif (5737 bytes)

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))


Search for Tips


All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

© Copyright 2016, J-Walk & Associates, Inc.
Privacy Policy