**Tom’s Tutorial For Excel: SUMIF for Multiple Criteria**

The `SUMIF`

function handles one criteria argument, which is great if you only need to sum for one criteria. But what if you want to sum for two or more criteria?

In the picture, a grocery store wants to know how many of each products sold, and also how many of a combination of products sold. You can use `SUMIF`

for one criteria as Example 1 shows, and you can add two criteria together with separate `SUMIF`

s as Example 2 shows.

But at some point you’ll need a better method. The `SUMPRODUCT`

function works well with the `SUMIF`

function to collect the criteria inside an *array constant*, which you can see from the curly braces in the formulas for Example 3 and Example 4. The beauty of array constants is, unless the formula you are constructing is an array formula, you can still commit the formula by simply pressing Enter, not by the Ctrl+Shift+Enter practice required for an array formula.

In Example 1, the formula for a single `SUMIF`

criteria is:

`=SUMIF(A2:A23,E4,B2:B23)`

In Example 2, the formula for two `SUMIF`

criteria is a redundant:

`=SUMIF(A2:A23,E9,B2:B23)+SUMIF(A2:A23,F9,B2:B23)`

In Example 3, the two criteria are hard-entered in an array constant:

`=SUMPRODUCT(SUMIF(A2:A23,{"Apples","Oranges"},B2:B23))`

In Example 4, you can see by further example how several more criteria can be inserted within the array constant. Three criteria would be a common practical ceiling to glean meaningful analysis from an aggregate sum, but you can add as many criteria as you need to in an array constant:

`=SUMPRODUCT(SUMIF(A2:A23,{"Apples","Oranges","Pears"},B2:B23))`

## Leave a Reply