SUMPRODUCT Function
Multiplies matching items in ranges and adds up the results.
Category
Math & Trig
Available in
All versions, Microsoft 365
Returns
A number
Overview
SUMPRODUCT multiplies row by row and then adds the answers — quantity × price for every line, then the grand total, in one formula.
Because it works on whole arrays, it's also a flexible conditional sum: conditions become 1s and 0s that switch rows on or off.
Syntax
Arguments
| Argument | Required? | What it does |
|---|---|---|
| array1 | Yes | The first range or array. |
| array2, … | Optional | More ranges the same size, multiplied item by item. |
Examples
Example 1 — Order total without a helper column
Quantity times price for every line, added up.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Qty | Price | Order total |
| 2 | Widget | 10 | 4.5 | 153 |
| 3 | Gadget | 4 | 12 | |
| 4 | Cable | 15 | 4 |
10×4.5 + 4×12 + 15×4 = 45 + 48 + 60 = 153.
Example 2 — Conditional total
Revenue (qty × price) for the West only.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Qty | Price | West revenue | |
| 2 | West | 10 | 4.5 | 105 | |
| 3 | East | 4 | 12 | ||
| 4 | West | 15 | 4 |
- A2:A4="West" gives TRUE, FALSE, TRUE.
- Multiplying turns those into 1, 0, 1 — East is switched off.
- 45 + 0 + 60 = 105.
Example 3 — Weighted average
Average price weighted by quantity.
Common mistakes
- Ranges must be the same size. Different-sized arrays return #VALUE!.
Notes

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.