How to Use SUMPRODUCT in Excel

Multiply matching rows and add the results in one formula — perfect for order totals and weighted scores.

You will learn to

• Multiply two columns row by row and total them
• Calculate a weighted average
• Add a condition inside SUMPRODUCT

How it works

SUMPRODUCT multiplies the values in two or more ranges row by row, then adds up the products. =SUMPRODUCT(B2:B6,C2:C6) is the same as =B2*C2+B3*C3+…+B6*C6, without a helper column.

Because a comparison like (A2:A6="Coffee") becomes 1 or 0 inside SUMPRODUCT, you can also use it for conditional totals and weighted averages: divide SUMPRODUCT(scores,weights) by SUM(weights).

=SUMPRODUCT(B2:B6,C2:C6)