Advertisment

Summing Based on Multiple Criteria

author-image
PCQ Bureau
New Update

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:

Advertisment
  • 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.

Advertisment

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

Advertisment

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:

Advertisment

=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.

Advertisment

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.

Advertisment

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.

Advertisment

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

Advertisment