Excel track · Exercise 75 of 86 Paid
SUMPRODUCT: multiply the pair, then add them up
Value a table and take a weighted average from the same two columns.
Advanced
Stock list with a price chain and a quantity per row
Read-only data · screen 1 of 2 · answer in D5
In D5, total the value of the stock: price times quantity on every row, added up.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Price | Qty | Value |
| 2 | pen | 10 | 2 | |
| 3 | pad | 5 | 4 | |
| 4 | ink | 20 | 3 | |
| 5 | Total stock |
In D5, work out the average price paid per item, counting each row once per unit it holds.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Price | Qty | Value |
| 2 | pen | 10 | 2 | |
| 3 | pad | 5 | 4 | |
| 4 | ink | 20 | 3 | |
| 5 | Total stock |
Reference answer
What it does
SUMPRODUCT pairs two ranges row by row, multiplies each pair, then adds the products. The formula =SUMPRODUCT(B2:B4,C2:C4) covers the three prices and the three quantities and returns 100. Ten times two, five times four and twenty times three come to 20, 20 and 60. A helper column of products would reach the same answer, in more steps and in a second place to be wrong.
The second screen puts that pair of ranges in the numerator of an average. Divide 100 by the quantity total of 9 and the answer is 11.1, the average price per item, with the ink row counted three times. Averaging the three prices instead comes to 11.7, because each row counts once whatever its quantity.
Keep both ranges on the same rows. SUMPRODUCT pairs by position, so a range that starts one row late pairs each price with the wrong quantity and the total still reads as a number. Excel only objects with #VALUE! when the heights differ outright.
Syntax
=SUMPRODUCT(prices, quantities) multiplies the two ranges row by row and adds the productsWorked example
On Stock list with a price chain and a quantity per row, =SUMPRODUCT(B2:B4,C2:C4) returns 100.
=SUMPRODUCT(B2:B4,C2:C4)Common errors
| Error | What it means and how to fix it |
|---|---|
#VALUE! | The two ranges are a different height, so the pairs stop lining up and Excel refuses the call. |
#NAME? | The function name is misspelt, so Excel reads it as an undefined name. |
Wrong total | A range that starts one row below the other pairs each price with the next quantity, and the total comes back as a number. |
At work
Valuing a stock list in one cell, and averaging a price per unit sold rather than per row.