Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

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.

Example of the SUMPRODUCT function to return total sales by sales rep when provided with sales and expenses for each.

  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

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×