Order total with SUMPRODUCT
In F1, work out the order total in one formula without a helper column. Use SUMPRODUCT to multiply the units by the prices row by row and add up the results.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Item | Units | Price | Order total | |||
| 2 | Pens | 10 | 4 | ||||
| 3 | Paper | 5 | 30 | ||||
| 4 | Ink | 2 | 85 | ||||
| 5 | Folders | 8 | 7 | ||||
| 6 | Tape | 6 | 12 | ||||
| 7 | |||||||
| 8 |
Need help?
Why this works
SUMPRODUCT multiplies the first cells of each range together, then the second ones, and so on, and adds up the products: 10×4 + 5×30 + 2×85 + 8×7 + 6×12 = 40 + 150 + 170 + 56 + 72 = 488. It saves a column of =B2*C2 formulas and a SUM under them.
Common mistake: Multiplying two totals, SUM(B2:B6)*SUM(C2:C6). That pairs every unit with every price, not each row with its own.
The answer
- F1
=SUMPRODUCT(1B2:B6,2C2:C6)
In plain words: Multiply 1the units in B2:B6 by 2the prices in C2:C6 row by row and add up the results.
It multiplies the matching cells of two or more ranges and adds up the results. =SUMPRODUCT(B2:B6,C2:C6) is the same as B2*C2+B3*C3+…+B6*C6.
Yes. Ranges of different sizes return #VALUE!.
+15 points