The SUMPRODUCT function in Excel
SUMPRODUCT multiplies matching items in two or more ranges, row by row, then adds up the results. That gives you total revenue from Qty and Price columns without a helper column, or a weighted score from scores and weights. With conditions inside it, it can also act like a flexible SUMIFS, which is why it shows up in so many Excel tests.
Syntax
=SUMPRODUCT(array1, [array2], ...)
| array1 | The first range of numbers, like B2:B6. |
|---|---|
| array2, ... | Optional. More ranges to multiply with the first. Every range must be the same size. |
Try it yourself
This is a live spreadsheet. Type the formula and press Enter.
A candidate was scored on five criteria. In E2, use SUMPRODUCT to calculate the weighted score: each score in C2:C6 times its weight in B2:B6, all added up.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Score (1-5) | Weighted score | |||||
| 2 | |||||||
| 3 | |||||||
| 4 | |||||||
| 5 | |||||||
| 6 | |||||||
| 7 | |||||||
| 8 | |||||||
| 9 |
Tip: SUMPRODUCT takes the two columns as separate arguments. The weights already add up to 1.
Examples
=SUMPRODUCT(B2:B6,C2:C6)→ 2442Multiplies Qty by Unit price on every row and adds them up: total revenue, with no extra column.
=SUMPRODUCT((D2:D6="North")*B2:B6*C2:C6)→ 1311The test in brackets is 1 for North rows and 0 for the rest, so only North revenue is added.
=SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6)→ 42.84210526A weighted average price: each price counts in proportion to the units sold.
Example data
| 1 | Product | Qty | Unit price | Region |
|---|---|---|---|---|
| 2 | Desk lamp | 14 | 34.5 | North |
| 3 | Monitor arm | 6 | 79 | South |
| 4 | Cable tray | 25 | 18 | North |
| 5 | Office chair | 3 | 219 | South |
| 6 | Footrest | 9 | 42 | North |
Common mistakes
Ranges of different sizes
SUMPRODUCT(B2:B6,C2:C7)returns #VALUE!. Every range must cover the same rows and columns.TRUE/FALSE passed as its own argument
Excel treats TRUE and FALSE as 0 inside SUMPRODUCT's separate arguments, so
SUMPRODUCT((D2:D6="North"),B2:B6)gives 0 in Excel. Multiply the test in with*, or turn it into 1s and 0s with--.Text in the ranges
Text and empty cells count as 0, so a quantity typed as text silently drops out of the total. Make sure the numbers are real numbers.
Including the header row
Starting the ranges at row 1 is harmless for text headers, but a numeric header (like a year) would be multiplied in. Start on the first data row.
FAQ
What does SUMPRODUCT do in simple terms?+
It multiplies each row's values together, then adds all those results. SUMPRODUCT(B2:B6,C2:C6) is the same as B2*C2+B3*C3+...+B6*C6.
How do I calculate a weighted average in Excel?+
Divide SUMPRODUCT by the total weight: =SUMPRODUCT(values,weights)/SUM(weights). If the weights already add up to 1, SUMPRODUCT alone is enough.
Should I use SUMPRODUCT or SUMIFS?+
SUMIFS is simpler to read for plain conditions. SUMPRODUCT is better when you need to multiply columns, use OR logic, or test a calculation like MONTH(A2:A50)=3.
Lesson
Master SUMPRODUCT step by step: Interview drill: HR roster
Work through a timed-test style HR roster: look up a salary, count, total and list by department, flag bonuses, calculate tenure and handle a missing ID.