Excel SUMPRODUCT Function
SUMPRODUCT Function
The SUMPRODUCT function is a premade function in Excel, which multiplies ranges item by item, and adds up the results.
For example, it can multiply the quantity by the price on every row of an order, and add it all up to an order total, in one formula.
It is typed =SUMPRODUCT and has the following parts:
=SUMPRODUCT(array1, [array2], [array3], ...)
Note: The different parts of the function are separated by a symbol, like comma , or semicolon ;
The symbol depends on your Language Settings.
Array1: The first range, for example a column of quantities.
Array2, array3, ...: Optional. More ranges to multiply with the first one.
SUMPRODUCT multiplies the first cells of all the ranges, then the second cells, and so on. Then it adds up all the results.
Note: All the ranges must have the same size. If one range has more rows than the others, SUMPRODUCT returns #VALUE!.
How to use the SUMPRODUCT function:
- Select a cell (
H2) - Type
=SUMPRODUCT - Double click the SUMPRODUCT command
- Mark the first range (
C2:C8) - Type (
,) - Mark the second range (
D2:D8) - Hit enter
Let's have a look at an example!
This is one order from the Poke Mart. The categories and prices are from the Poke Mart product list. Copy it and paste it into cell A1:
Example data
A B C D 1 Item Category Qty Price 2 Poke Ball Balls 10 200 3 Great Ball Balls 5 600 4 Ultra Ball Balls 2 1200 5 Potion Medicine 8 300 6 Super Potion Medicine 3 700 7 Revive Medicine 1 1500 8 Antidote Status Healers 4 100
Type the label Order Total in G2.
Use SUMPRODUCT to multiply Qty by Price on every row, and add up the results:
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Item | Category | Qty | Price | ||||
| 2 | Poke Ball | Balls | 10 | 200 | Order Total | 13800 | ||
| 3 | Great Ball | Balls | 5 | 600 | ||||
| 4 | Ultra Ball | Balls | 2 | 1200 | ||||
| 5 | Potion | Medicine | 8 | 300 | ||||
| 6 | Super Potion | Medicine | 3 | 700 | ||||
| 7 | Revive | Medicine | 1 | 1500 | ||||
| 8 | Antidote | Status Healers | 4 | 100 |
=SUMPRODUCT(C2:C8, D2:D8)
SUMPRODUCT calculates 10*200 + 5*600 + 2*1200 + 8*300 + 3*700 + 1*1500 + 4*100. The order total is 13800.
Without SUMPRODUCT, you need a helper column. Here, E2 has the formula =C2*D2, filled down to E8, and E9 adds up the line totals with =SUM(E2:E8):
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Category | Qty | Price | Line Total |
| 2 | Poke Ball | Balls | 10 | 200 | 2000 |
| 3 | Great Ball | Balls | 5 | 600 | 3000 |
| 4 | Ultra Ball | Balls | 2 | 1200 | 2400 |
| 5 | Potion | Medicine | 8 | 300 | 2400 |
| 6 | Super Potion | Medicine | 3 | 700 | 2100 |
| 7 | Revive | Medicine | 1 | 1500 | 1500 |
| 8 | Antidote | Status Healers | 4 | 100 | 400 |
| 9 | Total | 13800 |
The result is the same. SUMPRODUCT does it in one cell, without the helper column.
SUMPRODUCT with a Condition
To add up only some of the rows, add a condition as one of the ranges.
Find the total for the Balls category. Type the label Balls Total in G3:
| G | H | |
|---|---|---|
| 2 | Order Total | 13800 |
| 3 | Balls Total | 7400 |
=SUMPRODUCT(--(B2:B8="Balls"), C2:C8, D2:D8)
This is how it works:
B2:B8="Balls" checks every category, and gives a list of TRUE and FALSE.
SUMPRODUCT only multiplies numbers, and treats TRUE and FALSE as 0. The double minus -- turns them into real numbers first: TRUE becomes 1, and FALSE becomes 0.
The first minus turns TRUE into -1, and the second minus turns it back into 1.
Rows that are not Balls are multiplied by 0, so they add nothing to the total:
| Item | Category | B2:B8="Balls" | -- | Qty * Price | Result |
|---|---|---|---|---|---|
| Poke Ball | Balls | TRUE | 1 | 2000 | 2000 |
| Great Ball | Balls | TRUE | 1 | 3000 | 3000 |
| Ultra Ball | Balls | TRUE | 1 | 2400 | 2400 |
| Potion | Medicine | FALSE | 0 | 2400 | 0 |
| Super Potion | Medicine | FALSE | 0 | 2100 | 0 |
| Revive | Medicine | FALSE | 0 | 1500 | 0 |
| Antidote | Status Healers | FALSE | 0 | 400 | 0 |
| Sum | 7400 |
The total for Balls is 2000 + 3000 + 2400 = 7400.
Note: If you leave out the --, the condition counts as 0 on every row, and the result is 0.
Multiply Instead of --
Any calculation turns TRUE and FALSE into 1 and 0. So you can also multiply the condition with the other ranges. This formula gives the same result, 7400:
=SUMPRODUCT((B2:B8="Balls")*C2:C8*D2:D8)
You may also see (B2:B8="Balls")*1. Multiplying by 1 works like the double minus.
Two Conditions
Multiply two conditions to require both. A row only counts if both conditions are TRUE, because 1*1 is 1, and anything times 0 is 0.
Find the total for Balls where the Qty is 5 or more. Type the label Balls, Qty 5+ in G4:
| G | H | |
|---|---|---|
| 2 | Order Total | 13800 |
| 3 | Balls Total | 7400 |
| 4 | Balls, Qty 5+ | 5000 |
=SUMPRODUCT((B2:B8="Balls")*(C2:C8>=5), C2:C8, D2:D8)
Poke Ball (Qty 10) and Great Ball (Qty 5) match both conditions. Ultra Ball is a Ball, but the Qty is only 2. The total is 2000 + 3000 = 5000.
SUMPRODUCT or SUMIFS?
If your table already has a column with the line totals, like column E in the helper example, SUMIFS can add them up with conditions:
=SUMIFS(E2:E8, B2:B8, "Balls")
SUMPRODUCT is useful when the numbers must be multiplied first, and you do not want a helper column.
See also SUM, SUMIF and the multiplication operator.
