SUMPRODUCT function

This article describes the formula syntax and usage of the SUMPRODUCT function in Microsoft Excel.

Description

Performs simple arithmetic with corresponding components in the given arrays, and returns the sum of those calculations. By default, the operation is multiplication, but addition, subtraction, and division are also possible.

Syntax

To use the default operation (multiplication)

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

The SUMPRODUCT function syntax has the following arguments:

  • 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.

  • SUMPRODUCT treats array entries that are not numeric as if they were zeros.

Example

Sample grocery list to show how to use SUMPRODUCT

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

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

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×