SUMPRODUCT
Multiplies two (or more) ranges element by element, then sums the results, all in one formula.
The classic "total = sum of (quantity × price)" calculation is everywhere in invoicing, inventory, and weighted scoring, and the naive way to build it is a helper column: multiply each row's quantity by its price, then SUM that new column. SUMPRODUCT does both steps at once, multiplying each pair of rows and adding up the results, without a helper column ever existing on the sheet.
It's a genuine finance and modeling staple beyond simple invoices too: weighted averages, portfolio values, and labor costs (hours × rate, summed across a whole team) all follow the exact same shape.
Syntax
| Argument | Description |
|---|---|
| array1 | Required. The first range. |
| array2, ... | Required: at least one more range, the same size and shape as array1. |
A real example: an invoice total without a helper column
A simple order with a quantity column and a price column, totaled directly:
This is the formula a lot of people reach for a helper column to replace, then discover they didn't need one: SUMPRODUCT multiplies B2 by C2, B3 by C3, and so on, then adds all of those products together in a single cell.
Worked examples
| A | B | C | |
|---|---|---|---|
| 1 | Item | Qty | Price |
| 2 | Pens | 10 | 1.2 |
| 3 | Pads | 4 | 3.5 |
Common mistakes
- Mismatched range sizes between the arrays. SUMPRODUCT needs them to line up exactly.
- Reaching for a helper column out of habit when SUMPRODUCT would do it in one cell.
- Forgetting SUMPRODUCT sums the products. It doesn't return each product separately.
Related functions
Try it yourself
A free, graded practice exercise using this exact function: instant feedback, no signup required.
Practice SUMPRODUCT →