Excel Sum If With Multiple Criteria

There are many times that it become necessary to SUM cells based on multiple criteria. The example below will show you 2 ways that this can be done.

Symptom:

My formula works okay with just 1 criteria, but I can't get multiple criteria to work.

Range Data!C2:F11115
Criteria B5*data!c2:c11115<20000 f =" B5">

Hypothesis:

• Using * as AND can't be put in criteria?

• It seems the Criteria field are limited. The suggestion is adding a column with the IF condition you want to use, then sum that column.

• Using sumproduct function instead use sumif.

Procedure 1:

The best way to get multiple criteria to work over a range is not to use sumif but the sumproduct function.

=sumproduct( --(criteria 1) , --(criteria 2), range to sum over)

So for your example it would be something along the lines of

=SUMPRODUCT(--(Data!C2:C11115<20000),--(data!f2:f11115=staff!b5),data!j2:j11115) value =" value." value =" 0." style="font-weight: bold; color: rgb(255, 102, 102);">

Procedure 2:

If your wanting to do a single criteria then SUMPRODUCT(--(range=criteria)) or SUMPRODUCT(N(range=criteria)) can be used, but of course you can use COUNTIF for that so using double negatives or N becomes redundant. To count items then just use all the conditions within one argument like this... SUMPRODUCT((range=criteria)*(range=criteria)*(rang e=criteria)) to achieve a COUNT or include a second argument to indicate you want to sum the result if all the criteria in the first argument is true.... SUMPRODUCT((range=criteria)*(range=criteria)*(rang e=criteria),range ) for a SUM.

Result:

• Note you can have as many criteria as you like using the sumproduct function method!

• You need to have the double negative and the criteria in brackets for this to work. Arrays should also be of the same size too.

• When you have multiple criteria then * means AND and + means OR.