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

Excel Tips

Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.

In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.

## All Tips

## Browse Tips by Category

## Search for Tips

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