Tom’s Tutorials For Excel: SUMIF With Multiple Criteria

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