SUMIF – more than one condition

Q: I know how to use SUMIF with one matching criteria, but how can I use more than one?

By Jeff Robson
Short Answer: you can’t. SUMIF only accepts one criteria. Use the SUMIFS formula instead as it allows this (Excel 2007+)

Long Answer: you can still use SUMIF in this way if you insert a new column containing a logical function (eg AND, OR, NOT), or if you delve into the murky depths of array formulas.

Let’s look at each solution, assuming your data is arranged as shown below:

To use a Logical Function, insert a new column (eg at column D) then use one of the functions mentioned (or a combination) to do all the tests for you and produce a TRUE or FALSE result.

Example:

… and copy this down the column. Then use SUMIF to only sum those results that are TRUE (or false, depending on what you want of course).

Example:

If you don’t want to insert an extra column, you can use an array formula to achieve the same result.

Example:

To enter an array formula, don’t enter the curly brackets. Enter the formula as per normal but instead of pressing Enter, use Ctrl-Shift-Enter and Excel will automatically add the curly brackets.

Beware: Array formulas are very versatile (this is only one of their many uses) but they’re also very slow if you have a lot of them.