SUMPRODUCT explained: three examples you will use
SUMPRODUCT multiplies two lists and adds the results. A total of price times quantity, a weighted average, and a count with conditions, step by step.
SUMPRODUCT multiplies two lists row by row, then adds up the results.
=SUMPRODUCT(C2:C7,D2:D7) gives units times cost for every row,
added together, in one cell.
Here is a stock count from a small hardware shop. Each row is one item on the shelf. Column C is how many units are in stock. Column D is what one unit cost the shop.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Item | Aisle | Units | Cost |
| 2 | Screws (box) | 1 | 40 | 3.20 |
| 3 | Hinges | 1 | 25 | 4.50 |
| 4 | Paint (litre) | 2 | 12 | 9.75 |
| 5 | Brushes | 2 | 30 | 2.10 |
| 6 | Sandpaper (pack) | 3 | 50 | 1.80 |
| 7 | Drill bits (set) | 3 | 6 | 14.00 |
1. Units times cost, in one cell
The owner wants to know what the stock on the shelves is worth.
=SUMPRODUCT(C2:C7,D2:D7) gives 594.5.
SUMPRODUCT takes the first row of each range and multiplies them: 40 times 3.20 is 128. Then the second row: 25 times 4.50 is 112.5. It does this for all 6 rows and adds the 6 answers.
To see it work on a small piece, try =SUMPRODUCT(C2:C3,D2:D3). It
gives 240.5, which is 128 plus 112.5.
The other way is a helper column. Type =C2*D2 in E2, fill it down,
and total column E with SUM. You get the same 594.5.
Which is better? A helper column is easier to check, because you can see each line. Use it when other people read the sheet. Use SUMPRODUCT when you only need the one total.
2. A weighted average
Next question: what did one unit cost on average? Most people type
=AVERAGE(D2:D7). It gives 5.89, and that answer is wrong.
AVERAGE counts each row once. The 6 drill bit sets at 14.00 count as much as the 50 packs of sandpaper. But the shop holds far more cheap items than expensive ones.
=SUMPRODUCT(C2:C7,D2:D7)/SUM(C2:C7) gives 3.647, which shows as
3.65 with 2 decimal places.
That is the value of all the stock, 594.5, divided by the number of units, 163. Each row now counts as many times as it has units.
The same shape works anywhere one number matters more than another. Think of a course grade where the exam counts more than the homework. Put the weights in one range and the scores in the other.
3. A count with conditions
Last question: how many lines are worth more than 100? No column holds the value of a line. It is units times cost, so COUNTIF cannot test it.
=SUMPRODUCT((C2:C7*D2:D7>100)*1) gives 3.
The part in brackets works out the value of each line and tests it. That gives 6 answers, each TRUE or FALSE. The *1 turns TRUE into 1 and FALSE into 0. SUMPRODUCT then adds them up.
Leave out the *1 and =SUMPRODUCT((C2:C7*D2:D7>100)) gives 0.
SUMPRODUCT does not count TRUE as a number. There is no error, only a wrong 0.
For two conditions, multiply the tests. This counts the lines in aisle 1 worth more than 100:
=SUMPRODUCT((B2:B7=1)*(C2:C7*D2:D7>100)) gives 2.
Screws and hinges pass both tests. Paint is worth 117 but sits in aisle 2. The two tests multiply to 1 only when both are TRUE, so no *1 is needed here.
You can add instead of count. Put the numbers in the product too.
=SUMPRODUCT((B2:B7=2)*C2:C7*D2:D7) gives 180, the value of
everything in aisle 2.
SUMPRODUCT or SUMIFS?
Use SUMIFS unless you need to test or add a calculation. To total the units in aisle 2,
=SUMIFS(C2:C7,B2:B7,2) gives 42. It is shorter and easier to read.
SUMIFS needs a range for everything it tests and everything it adds. When the thing you need is units times cost, there is no such range. That is where SUMPRODUCT is the right tool. The SUMIF and SUMIFS exercises cover the simple cases.
What goes wrong in real files
Ranges of different sizes.
=SUMPRODUCT(C2:C7,D2:D6) gives
#VALUE!. Each range needs the same number of rows. This happens
after someone adds a row and changes only one range.
The header row inside the range. With commas,
=SUMPRODUCT(C1:C7,D1:D7) still gives 594.5. SUMPRODUCT treats
the words in row 1 as 0. With a star, =SUMPRODUCT(C1:C7*D1:D7)
gives #VALUE!, because you cannot multiply a word.
A number stored as text. Say C5 came from an export as the text "30", not the number 30. The comma form treats it as 0. The total drops to 531.5 and there is no error. The article on numbers stored as text shows how to find them.
Questions people ask
Can I use SUM instead of SUMPRODUCT?
In Excel 2021, Excel 2024 and Microsoft 365, yes.
=SUM(C2:C7*D2:D7) gives 594.5 too. In Excel 2019 and older, it
needs Ctrl+Shift+Enter. SUMPRODUCT works in every version with a normal Enter.
What does the double minus mean?
You will see =SUMPRODUCT(--(C2:C7*D2:D7>100)) in other
people's files. It gives 3, the same as the *1 version. Two minus signs turn TRUE into 1 and
FALSE into 0. Use the one you find easier to read.
Can SUMPRODUCT take more than two ranges?
Yes. It multiplies all of them, row by row. Every range must still have the same number of rows.
Where can I practise this?
In the SUMPRODUCT lesson below. Each lesson is a real sheet you type into, and your answer is checked when you press Enter.