Excel function guide · Math & rounding
SUMPRODUCT function in Excel
SUMPRODUCT multiplies matching items in two or more ranges and adds up the results, for example units × price on every row for total revenue, without a helper column. Multiplying by a condition such as (D2:D5="North") makes it a conditional total.
Syntax
=SUMPRODUCT(array1, [array2], …)
=SUMPRODUCT((condition_range="value")*array1*array2)
| Argument | Required? | What it means |
|---|---|---|
| array1 | Required | The first range (or a calculation that produces one). |
| array2, … | Optional | More ranges of the same size. Items in the same position are multiplied, then all the products are added. |
SUMPRODUCT examples
Every example uses this small table. The results are what Excel shows in the cell.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Units | Price | Region |
| 2 | Desk lamp | 120 | 39 | North |
| 3 | Office chair | 35 | 240 | South |
| 4 | Monitor arm | 64 | 85 | North |
| 5 | Webcam | 88 | 65 | South |
1. Total revenue
=SUMPRODUCT(B2:B5, C2:C5)
Result: 24,240
120×39 + 35×240 + 64×85 + 88×65, without a revenue column.
2. With a condition
=SUMPRODUCT((D2:D5="North")*B2:B5*C2:C5)
Result: 10,120
The comparison gives TRUE or FALSE per row; multiplying turns them into 1 or 0, so only North rows count: 4,680 + 5,440.
3. A number condition
=SUMPRODUCT((B2:B5>50)*B2:B5*C2:C5)
Result: 15,840
Revenue from lines of more than 50 units: the office chair line (35 units) is left out.
Try it now
Practise SUMPRODUCT in a live sheet.
Type each formula yourself. Answers are checked by the same engine as the full course. No account needed.
Step 1 of 3
In F2, calculate total revenue (Units × Price on every row, added up) with one SUMPRODUCT.
Common SUMPRODUCT mistakes
Ranges of different sizes
Every array must have the same dimensions (B2:B5 with C2:C5, not C2:C6), or SUMPRODUCT returns #VALUE!.
Conditions as separate arguments
SUMPRODUCT(D2:D5="North", B2:B5) treats TRUE/FALSE as zero, so the answer is 0. Multiply the condition in: (D2:D5="North")*B2:B5.
Whole-column references
SUMPRODUCT(A:A, B:B) multiplies over a million rows and slows the workbook down. Use ranges that fit the data, or a table.
Using it where SUMIFS is clearer
For a plain conditional total of one column, SUMIFS is easier to read. SUMPRODUCT earns its place when you need to multiply columns or use conditions SUMIFS can't express.
Practise this in Excel Quest
ROUND, ROUNDUP & ROUNDDOWN Members
Course 2 · Essential Functions · 9 min
Set clean prices, ship whole boxes and quote round numbers — choosing the right rounding for each job. SUMPRODUCT appears in the ROUND, ROUNDUP and ROUNDDOWN lesson, where you total an order with it and round the result for a quote.
This lesson is included in Formula Quest memberships. A free account starts you on the free lessons of the Excel Quest curriculum.