Excel: Correctly calculate conditional sums
In Excel you can calculate sums that do not take into account all elements of the selected area - so-called conditional sums. We'll show you how to make the most sensible calculation.
Three different functions for conditional sums in Excel
Excel offers various options and functions for conditional totals. The right choice depends on the type of conditions:
- If a fixed condition applies to each value in question, use the SUMIF function.
- If several conditions have to be taken into account, there is also the SUMIFS function as of Excel 2007.
- With variable conditions, however, you need a matrix formula or the SUM PRODUCT function.
- In the following paragraphs we explain the three functions using examples.
SUMIF: Conditional sums with fixed conditions
- Assume that the data is in cells A1 to A10 and various values in cells B1 to C10 that are used for conditions.
- The first task with a fixed condition then means, for example, to sum up all the values in column A for which the respective value in column B is approximately greater than 10.
- To do this, use the formula »= SUMIF (B1: B10;"> 10 "; A1: A10)«.
- The first argument »B1: B10« defines the area to be evaluated, followed by the condition »"> 10 "«. Note that conditions with symbols or operators must be in quotation marks.
- Finally, »A1: A10« defines the area to be summed.
- The ranges for conditions and dates can also be identical, so that the formula »= SUMIF (A1: A10;"> 10 ")« is sufficient.
SUM IF: Conditional sums with multiple conditions
As of Excel 2007, the SUMIFS function extends this principle to several to a maximum of 127 conditions:
- Note the other order of the arguments. The area to be summed is here first. This is followed by a criteria area and the associated condition, whereby the areas must always be specified separately.
- For example, the formula "= SUMIFS (A1: A10; B1: B10;"> 10 "; C1: C10;" <100 ")« sums up all values in column A for which the corresponding values in column B are greater than 10 and the corresponding values in column C are less than 100.
SUM PRODUCT: Conditional sum with variable conditions
Variable criteria such as "if the value in column B is greater than that in column C" require a matrix formula for evaluation:
- The values to be taken into account are selected by multiplying by a logic value.
- To do this, enter about "= SUM (A1: A10 * (B1: B10> C1: C10))" and always close the entry, even after a change with the key combination [Ctrl] + [Shift] + [Enter], so that the formula is in braces.
- In this case, you can achieve the same result with the function "= SUM PRODUCT (A1: A10; 1 * (B1: B10> C1: C10))".
- However, you must note that you first have to multiply the second argument by 1 in order to convert the logic value of the comparison into a number.
Note: In practically all cases, you can clearly create the total with an auxiliary column. This is also advisable during the development of a calculation to check the results of the complex formulas. Tip: We will explain how to add many cells in Excel here.