SUMPRODUCT function

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

Description

Multiplies corresponding components in the given arrays, and returns the sum of those products.

Syntax

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.

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.

×