SUMPRODUCT Generator
Multiply matching cells in two ranges and add all the products in one cell.
SUMPRODUCT
Multiply matching cells in two ranges and add all the products in one cell.
First set of numbers, for example quantity per row.
Second set of numbers, for example unit price. Must be exactly the same size as the first range.
=SUMPRODUCT(B2:B100, C2:C100)Worked Example
An invoice where column B holds quantities and column C holds unit prices for rows 2 through 100.
=SUMPRODUCT(B2:B100, C2:C100)Returns: Returns B2*C2 + B3*C3 + ... + B100*C100 as a single number - the invoice total, with no extra line-total column needed.
Checks Before You Paste
- •The ranges must be identical in shape. SUMPRODUCT(B2:B100, C2:C101) returns #VALUE!, and so does mixing a whole-column reference like B:B with a bounded range.
- •Comparisons return TRUE and FALSE, which Excel's SUMPRODUCT treats as non-numeric and effectively counts as zero. Coerce them to 1 and 0 with a double minus or by multiplying: =SUMPRODUCT(--(A2:A100="West"), C2:C100) or =SUMPRODUCT((A2:A100="West")*(C2:C100)). Both forms behave correctly in Google Sheets too.
- •Text and blanks inside a range are treated as zero rather than raising an error, but a real error value such as #N/A anywhere in the range propagates and takes the whole result with it. Clean the data or wrap the offending range in IFERROR, as in =SUMPRODUCT(IFERROR(B2:B100, 0), C2:C100).