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:

articleid_120_sumifmorethanonecondition_4

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:

articleid_120_sumifmorethanonecondition_1

… 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:

articleid_120_sumifmorethanonecondition_2

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

Example:

articleid_120_sumifmorethanonecondition_3

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.

SUMPRODUCT

You could also use the SUMPRODUCT function to achieve similar results, without requiring the use of array formulas.

e.g. =SUMPRODUCT((A2:A4=”July”)*(B2:B4=”Monday”)*C2:C4)

Extras

Excel 2007 introduced the SUMIFS function for multi-criteria SUMIFs and this makes life a whole lot easier!