Total a purchase order without a helper column
SUMPRODUCT multiplies quantity by cost row for row and adds it all up in a single formula — no line-total column needed.
Task:
You work in procurement at a manufacturing company, checking a supplier's invoice against the purchase order before it gets approved for payment. The order has four line items, each with its own quantity and unit cost, and nobody wants a helper column of line totals cluttering the sheet just to get one number. Work out the total cost of the order in B6 using a single SUMPRODUCT formula.
Learning Objectives:
- Multiply two ranges element by element and sum the result with a single SUMPRODUCT formula
- Skip a helper column of line totals when only the grand total is needed
- See why a formula with no intermediate cells resists going stale in a way a helper column doesn't
Solve without hints for +5 XP
Interactive Spreadsheet
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | |
|---|---|---|---|
| 1 | Item | Quantity | Unit Cost |
| 2 | Steel bolts (M8) | 500 | 0.12 |
| 3 | Washers | 1200 | 0.03 |
| 4 | Hex nuts | 800 | 0.05 |
| 5 | Threaded rod (3m) | 40 | 4.75 |
| 6 | Total order cost |
What this exercise teaches (contains the answer)
SUMPRODUCT pairs up B2:B5 and C2:C5 position by position — B2 with C2, B3 with C3, and so on — multiplies each pair, and adds the four results together in one step, which is the same arithmetic a line-total column would do, just without a column to hold it. That matters here because a helper column is one more place the sheet can go stale: add a fifth line item and forget to fill its line-total cell down, and the grand total below it silently undercounts. SUMPRODUCT can't be half-updated that way — extend both ranges to cover the new row and the total is right, or the ranges are wrong and the total is visibly short a row's worth of cost.