Tom’s Tutorial For Excel: SUMIF for Multiple Criteria
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:
In Example 2, the formula for two
SUMIF criteria is a redundant:
In Example 3, the two criteria are hard-entered in an array constant:
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: