Excel functions / Maths & statistics
SUMPRODUCT
Multiplies ranges together row by row and adds up the results.
Syntax
=SUMPRODUCT(array1, [array2], ...)Arguments
array1, array2, ... | Ranges of the same size to multiply together. |
|---|
How it works
SUMPRODUCT does in one cell what would otherwise take a helper column. Given quantities and prices, it multiplies each pair and adds the lot, giving the total value.
It is also the usual way to work out a weighted average: =SUMPRODUCT(scores,weights)/SUM(weights).
Example
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Qty | Price | ||
| 2 | Pen | 2 | 3 | 36 | |
| 3 | Pad | 4 | 5 | ||
| 4 | Ink | 1 | 10 | ||
| 5 |
Result: 36. 2×3 + 4×5 + 1×10.
Tips
- Every range must be the same size, or you get #VALUE!.
Practise SUMPRODUCT
Try it yourself in a real spreadsheet, with instant feedback on every answer.