Summing Based on Multiple Criteria

We’ll look at a useful feature in Excel, which is to sum entries based on multiple conditions, ie, sum the entries in the range D2:D5 (refer figure) where:

• The employee code is >12
• The department is Corporate AND
• The entry is authorized

This algorithm can best be explained with an example, and best understood if you try it out yourself in Excel. So let’s get started. Before we proceed, it is imperative to understand the process of summing when there is no condition or one condition.

When there is no condition, then summing is as simple as selecting the range D2:D5 and clicking on the S icon. A shortcut for summing is pressing Alt and +/= keys together in cell D6. The result in cell D6 is 178.

One condition would happen when we want to sum the entries in range D2:D5 for those cases where the entry is authorized, ie, where the entry in range C2:C5 is y. Excel has a built in function called sumif to take care of such a situation. The syntax of the sumif function is:

SUMIF(range,criteria,sum_range)1

With respect to the data in the figure, range (in the sumif formula) would be C2:C5. Criteria would be “y” and sum_range would be D2:D5. Hence the formula in cell D6 would be as follows:

=Sumif(C2:C5,”y”,D2:D5) 

The result of the formula is 122. Extending the same logic , it is possible to sum the entries in the range D2:D5 for those cases where the employee code more than 12 by entering the following formula:

=Sumif(A2:A5,“>12”,D2:D5)

Problem defined

As explained above, the sumif function can be used only when there is one condition, ie, if we want the entries in the range D2:D5 summed in situations where the employee code is > 12. However, if we want additional criteria such as ‘the department is corporate and the entry is authorized’, then the sumif function cannot be used.

Workaround

To workaround the problem of summing based on multiple criteria, we employ array formulas (Advance formulae in Excel, page 40, January 2004 ) where we specify multiple conditions.

Syntax 

The syntax of the formula is 



Sum(if((condition1)+/*(condition2)+/*(condition3),sum_range,0)). 

Since it is an array formula, press Ctrl+Shift+Enter in cell D6. It is worthwhile to note the operator between two conditions (+ or *). Here + indicates the OR condition and * indicates the AND condition.

Interpretation

In this formula, the * indicates that the conditions preceding and succeeding the operator should both be satisfied to sum the entries in the range D2:D5. Even if one condition is unsatisfied, the formula returns a 0.

The + indicates that even if one condition is satisfied, entries in the range D2:D5 are summed. If none of the conditions are satisfied, then the formula returns a 0. 

Algorithm

The algorithm for the AND condition is:



=SUM(IF(($A$2:$A$5>$A$2)*($B$2:$B$5=$B$2)*($C$2:$C$5=$C$3),$D$2:$D$5,0))



The above formula can be interpreted as: Sum the amounts in range D2:D5, if the employee code is >12, AND the department is Corporate AND the entry is authorized. The result then is 56.

If the entry in cell C5 is N, then the result of the formula would be 66.

The algorithm for the OR condition is the same as the one above, only the * has to be replaced with a +. This formula says to sum up the amounts in range D2:D5, if the employee code is >12 OR the department is Corporate OR the entry is authorized.
The result is 178.

Varying the conditions in the formula

This algorithm can be suitably modified to incorporate other conditions as well. It instead of taking employees with age >12, we take employees between the age of 12 and 55. 

This condition can be fulfilled by simply adding ‘($A$2:$A$5 <55)’ to the AND formula above. The formula would then become:



=SUM(IF(($A$2:$A$5>$A$2)*($B$2:$B$5=$B$2)*($C$2:$C$5=$C$3)* ($A$2:$A$5<55),$D$2:$D$5,0))

Combining the OR and AND conditions

Now let’s take another example, where Excel should sum the range D2:D5 under the following conditions:

• Employee code is > 12
• Department is Corporate OR Treasury AND
• The entry is authorized 

This is a case of combining the OR and AND statements. The algorithm would now become as follows: 



=SUM(IF(($A$2:$A$5>$A$2)*(($B$2:$B$5=$B$2)+($B$2:$B$5=$B$4))*($C$2:$C$5=$C$3),$D$2:$D$5,0))

Now change the entry in B5 to Treasury and C5 to N. The result is 66.

Ashish Mathur