Saturday, November 7, 2020

Sumproduct function

The SUMPRODUCT function returns the sum of the products of corresponding ranges or arrays. The default operation is multiplication, but addition, subtraction, and division are also possible.

In this example, we'll use SUMPRODUCT to return the total sales for a given item and size:

Example of using the SUMPRODUCT function to return total sales when provided with product name, size and individual sales values for each.

SUMPRODUCT matches all instances of Item Y/Size M and sums them, so for this example 21 plus 41 equals 62.

Syntax

To use the default operation (multiplication):

=SUMPRODUCT(array1, [array2], [array3], ...)

The SUMPRODUCT function syntax has the following arguments:

Argument

Description

array1   

Required

The first array argument whose components you want to multiply and then add.

[array2], [array3],...    

Optional

Array arguments 2 to 255 whose components you want to multiply and then add.

To perform other arithmetic operations

Use SUMPRODUCT as usual, but replace the commas separating the array arguments with the arithmetic operators you want (*, /, +, -). After all the operations are performed, the results are summed as usual.

Note: If you use arithmetic operators, consider enclosing your array arguments in parentheses, and using parentheses to group the array arguments to control the order of arithmetic operations.

Remarks

  • The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value. For example, =SUMPRODUCT(C2:C10,D2:D5) will return an error since the ranges aren't the same size.

  • SUMPRODUCT treats non-numeric array entries as if they were zeros.

  • For best performance, SUMPRODUCT should not be used with full column references. Consider =SUMPRODUCT(A:A,B:B), here the function will multiply the 1,048,576 cells in column A by the 1,048,576 cells in column B before adding them. 

Example 1

Example of the SUMPRODUCT function used to return the sum of items sold when provided unit cost and quantity.

To create the formula using our sample list above, type =SUMPRODUCT(C2:C5,D2:D5) and press Enter. Each cell in column C is multiplied by its corresponding cell in the same row in column D, and the results are added up. The total amount for the groceries is $78.97.

To write a longer formula that gives you the same result, type =C2*D2+C3*D3+C4*D4+C5*D5 and press Enter. After pressing Enter, the result is the same: $78.97. Cell C2 is multiplied by D2, and its result is added to the result of cell C3 times cell D3 and so on.

Example 2

The following example uses SUMPRODUCT to return the total net sales by sales agent, where we have both total sales and expenses by agent. In this case, we're using an Excel table, which uses structured references instead of standard Excel ranges. Here you'll see that the Sales, Expenses, and Agent ranges are referenced by name.

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

The formula is: =SUMPRODUCT(((Table1[Sales])+(Table1[Expenses]))*(Table1[Agent]=B8)), and it returns the sum of all sales and expenses for the agent listed in cell B8.

Example 3

In this example, we want to return the total of a particular item sold by a given region. In this case, how many cherries did the East region sell?

Exampe of using SUMPRODUCT to return the sum of items by region. In this case, the number of cherries sold in the East region.

Here, the formula is: =SUMPRODUCT((B2:B9=B12)*(C2:C9=C12)*D2:D9). It first multiplies the number of occurrences of East by the number of matching occurrences of cherries. Finally, it sums the values of the corresponding rows in the Sales column. To see how Excel calculates this, select the formula cell, then go to Formulas > Evaluate Formula > Evaluate.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Perform conditional calculations on ranges of cells

Sum based on multiple criteria with SUMIFS

Count based on multiple criteria with COUNTIFS

Average based on multiple criteria with AVERAGEIFS

No comments:

Post a Comment