The Excel Conditional Sum Function
Conditional Summing in Excel is a technique which allows the user to calculate a value as a sum of a collection of cells based on the evaluation of the cell value. For example, if we wish to sum only those values that are above average, we can use one of the conditional sum functions to do so.
The conditional sum functions are a group of three:
- SUMIF
- COUNTIF
- IF
Quite complex cell formulae can be built up using nested SUMIF, IF and COUNTIF combinations. In some cases, it is better to write a VBA (Visual Basic for Applications) macro to perform the task, especially if we want to combine it with changing cell properties (color, background, etc.).
Those not familiar with logical expressions in computer programming might want to take 5 minutes now to go and look at the Condition Testing article. It covers the 'criteria' (in Excel speak) that we can test in order to perform a conditional sum.
The Range is the range of cells to test. These can be columns, or rows, but not mixed. We must always sum ranges that are in the same direction as the condition ranges. The Criteria tell Excel what to test each cell in Range against.
Criteria can be any constant value, combined with an operator (>, <, =, >< etc.). If an operator is used, it is usual to enclose the expression in quotes. It is not possible to put callable functions, or other conditions that need to be evaluated in this parameter - only constant values to test against.
The last parameter, Range to Sum is optional. If it is not given, then the conditional sum will be applied to the cells in the original Range. Thus, the simples form of conditional sum is:
This will sum the contents of cells A2 to A5 (row by row), if the cell contents are greater than 3. We could also use the form:
This example would sum cells in the range B2 to B5, cell by cell, if the contents of the equivalent cell in the A column (A2 to A5) is equal to 7. If the ranges are not of equal size, Excel will not flag an error in the formula, but the result will not necessarily be correct.
One thing we can not do is:
In this example, the range in bold is incorrect, as it does not match the range that is being tested. Excel will not flag an error, however.
The COUNTIF function counts the number of cells in Range that match with Criteria. The restrictions are the same as for the simple form of SUMIF, above.
Finally, the IF function provides another dimension to summing. The Test is any comparison of constants, functions, or evaluations that can be tested, from simple cell references to Excel functions such as Average, Median and so on.
The True parameter is the value of the cell, should the Test return True, while the False parameter is the value of the cell, should the Test return False. It is the equivalent to an if ... then ... else condition in programming.
To use IF in conditional summing, we have to be aware that it can only be used to derive the value for a single cell. That is, it is not iterative, and cannot iterate over multiple cells in a range. An example of a conditional sum using if might be:
This essentially says that if the average of column A is less than the average of column B, then the call containing the IF statement should be set to the sum of column A. If not, then it should be set to the sum of column B. These IF statements can be nested up to 7 deep, but at that point, the user should probably consider a macro instead.
