This article describes the formula syntax and usage of the SUMPRODUCT function in Microsoft Excel.
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.
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.
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.
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.