SUMPRODUCT
Evan Paulus
Most Excel users, even "power" users, have a limited set of functions that they use the majority of the time. Although each users most useful functions might vary slightly, there are some classics that just about everyone uses from time to time. Some we've described already (See the SUMIF and SUMIFS and VLOOKUP and INDEX/MATCH posts), and still others will come in future posts.
One such function is SUMPRODUCT, which is particularly useful when you have a table that contains an inventory of goods or purchases, or both.
For example, the below table shows a receipt for a dinner party at a local restaurant. The SUMPRODUCT function makes calculating the total simple.
| A | B | C | D | |
| 1 | Guest | Item | Quantity | Item Cost |
|---|---|---|---|---|
| 2 | Sally | Drink | 3 | $4.50 |
| 3 | Sally | Appetizer | 1 | $13.00 |
| 4 | Sally | Salad | 1 | $8.00 |
| 5 | Amy | Drink | 2 | $2.50 |
| 6 | Amy | Entree | 1 | $22.50 |
| 7 | James | Drink | 4 | $6.00 |
| 8 | James | Salad | 1 | $8.00 |
| 9 | James | Entree | 1 | $17.50 |
| 10 | Brandon | Drink | 3 | $3.50 |
| 11 | Brandon | Appetizer | 2 | $9.00 |
| 12 | Brandon | Dessert | 1 | $6.00 |
| 13 | ||||
| 14 | Value | Formula | ||
| 15 | $120.50 | =SUMPRODUCT(C2:D12) |
What the SUMPRODUCT function is doing in the spreadsheet above is multiplying each item (Quantity and Item Cost) by each other and then adding them. Sally bought three drinks at $4.50 each so, in total, her drink cost was $13.50. Add that to the one appetizer @ $13.00 that she bought and so forth. The total bill for the party of four this evening was $120.50.