SUMPRODUCT

Try it

When to use it

When should I use SUMPRODUCT instead of multiplying then summing separately?

SUMPRODUCT multiplies arrays element by element and adds the results in one step, so you avoid a helper column. It is ideal for weighted totals like quantity times price, or score times weight.

Can SUMPRODUCT compute a weighted average?

Yes. Divide a SUMPRODUCT of values and weights by the sum of the weights: SUMPRODUCT(values, weights) divided by SUM(weights) gives the weighted mean.

Use cases

Weighted sum of items at different rates

Portfolio value from positions and weights

Used in

operations

Syntax

SUMPRODUCT(array1, [array2], ...)

Examples

=SUMPRODUCT({1,2,3,4},{5,6,7,8}) // => 70
=SUMPRODUCT({2,3,4},{1,2,3}) // => 20
=SUMPRODUCT({1,2,3,4}) // => 10