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