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.
… 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).
If you don’t want to insert an extra column, you can use an array formula to achieve the same result.
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.
You could also use the SUMPRODUCT function to achieve similar results, without requiring the use of array formulas.
Excel 2007 introduced the SUMIFS function for multi-criteria SUMIFs and this makes life a whole lot easier!