What SUMPRODUCT does
SUMPRODUCT pairs entries by position, multiplies them, and totals the results. It is useful for order costs and weighted calculations without a separate product column. Arithmetic operators can also specify addition, subtraction, or division before the final sum.
Practical examples
Total an order from quantities and prices
Enter these headings in A1:B1 and the numbers in A2:B4:
| A: Quantity | B: Unit price |
|---|---|
| 2 | 10 |
| 3 | 20 |
| 4 | 5 |
=SUMPRODUCT(A2:A4,B2:B4)
The result is 100: 2×10 + 3×20 + 4×5 = 20 + 60 + 20.
Give the final assessment twice the weight
On a separate sheet, enter these headings in A1:B1 and the numbers in A2:B4:
| A: Score | B: Weight |
|---|---|
| 70 | 1 |
| 80 | 1 |
| 90 | 2 |
=SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4)
The weighted score is 82.5: (70×1 + 80×1 + 90×2) / (1+1+2) = 330/4. The SUM function totals the weights. Their total must be nonzero to avoid division by zero.
Common mistakes and notes
Match both dimensions and positions
Different array dimensions cause #VALUE!. Equal-sized ranges can still pair the wrong records if one starts a row too low.
Check imported numbers
With separate range arguments as above, nonnumeric entries count as zero. A text score with a numeric weight can therefore lower the weighted average unexpectedly.
Limit the calculation to the data
Whole-column references process every row and can slow recalculation. Use bounded ranges such as A2:A1000.
Keep operation order clear
When replacing commas with arithmetic operators, use parentheses to group the calculations you intend to perform before summing.