How to use SUMPRODUCT in Excel
Multiplies ranges item by item, then adds the results.
Syntax
array1A range or array[array2]Another range of the same size optionalExample
Here’s SUMPRODUCT on a small table of orders. The highlighted cells are the ones the formula reads.
=SUMPRODUCT(D2:D9,E2:E9)Edit this formula live Change the ranges and watch the answer update. Free, no sign-up.
Common mistakes with SUMPRODUCT
Ranges must be the same size
Every array must have the same number of rows and columns, or you get #VALUE!.
Great for weighted totals
SUMPRODUCT(quantity, price) multiplies row by row and adds the results in one step.
What the error messages mean
Type these slips into Gridwise and this is the feedback you get:
Leaving off the closing bracket
=SUMPRODUCT(D2:D9,E2:E9
Parentheses don’t match. You have 1 opening and 0 closing parentheses. Every ( needs a ).
Questions
What does the SUMPRODUCT function do in Excel?
Multiplies ranges item by item, then adds the results. The syntax is SUMPRODUCT(array1, [array2], …).
Why is my SUMPRODUCT formula giving the wrong answer?
Ranges must be the same size: Every array must have the same number of rows and columns, or you get #VALUE!. Great for weighted totals: SUMPRODUCT(quantity, price) multiplies row by row and adds the results in one step.
Does SUMPRODUCT work in Google Sheets?
Most Excel functions, including the core ones on this page, work the same way in Google Sheets. A few newer functions arrived in Sheets later or behave slightly differently, so test it on your own data.