Perform conditional calculations on ranges of cells

When you need to perform simple arithmetic calculations on several ranges of cells, sum the results, and use criteria to determine which cells to include in the calculations, consider using the SUMPRODUCT function.

SUMPRODUCT takes arrays and arithmetic operators as arguments. You can use arrays that evaluate as True or False (1 or 0) as criteria by using them as factors (multiplying them by the other arrays).

For example, suppose you want to calculate net sales for a particular sales agent by subtracting expenses from gross sales, as in this example.

1. Click a cell outside the ranges you are evaluating. This is where your result goes.

2. Type =SUMPRODUCT(.

3. Type (, enter or select a range of cells to include in your calculations, then type ). For example, to include the column Sales from the table Table1, type (Table1[Sales]).

4. Enter an arithmetic operator: *, /, +, -. This is the operation you will perform using the cells that meet any criteria you include; you can include more operators and ranges. Multiplication is the default operation.

5. Repeat steps 3 and 4 to enter additional ranges and operators for your calculations. After you add the last range you want to include in calculations, add a set of parentheses enclosing all the involved ranges, so that the entire calculation is enclosed. For example, ((Table1[Sales])+(Table1[Expenses])).

   You may need to include additional parentheses inside your calculation to group various elements, depending on the arithmetic you want to perform.

6. To enter a range to use as a criterion, type *, enter the range reference normally, then after the range reference but before the right parenthesis, type =", then the value to match, then ". For example, *(Table1[Agent]="Jones"). This causes the cells to evaluate as 1 or 0, so when multiplied by other values in the formula the result is either the same value or zero - effectively including or excluding the corresponding cells in any calculations.

7. If you have more criteria, repeat step 6 as needed. After your last range, type ).

   Your completed formula might look like the one in our example above: =SUMPRODUCT(((Table1[Sales])-(Table1[Expenses]))*(Table1[Agent]=B8)), where cell B8 holds the agent name.

SUMPRODUCT function

Sum based on multiple criteria with SUMIFS

Count based on multiple criteria with COUNTIFS

Average based on multiple criteria with AVERAGEIFS