Turn three weighted criteria into one vendor score
SUMPRODUCT multiplies matching entries from two ranges and adds the results — a weighted score in one step instead of three multiplications and a sum.
Task:
You handle vendor selection for a manufacturing company's procurement team. Four vendors have been scored out of 10 on quality, price and delivery, and the weight each criterion carries toward the final decision sits in row 2 — 40% quality, 35% price, 25% delivery. Work out each vendor's overall weighted score in column E, in one formula that pairs each vendor's three scores with the matching weights.
Learning Objectives:
- Use SUMPRODUCT to multiply two ranges element-by-element and sum the results in one formula
- Compute a weighted total without a helper column for each product term
- Lock a weights row with absolute references so it survives being filled down
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 | D | E | |
|---|---|---|---|---|---|
| 1 | Vendor | Quality | Price | Delivery | Weighted Score |
| 2 | Weight | 0.4 | 0.35 | 0.25 | |
| 3 | Meridian Supply | 8 | 6 | 9 | |
| 4 | Coastal Parts | 7 | 9 | 6 | |
| 5 | Ironclad Components | 9 | 5 | 8 | |
| 6 | Summit Fabrication | 6 | 8 | 7 |
What this exercise teaches (contains the answer)
SUMPRODUCT(B3:D3,$B$2:$D$2) multiplies each score by the weight in the same relative position — B3 by B2, C3 by C2, D3 by D2 — and adds the three products, which is exactly what a weighted score is arithmetically, just without three multiplication cells to keep synchronized. Writing =B3*B2+C3*C2+D3*D2 would return the same number for one vendor, but it stops working the moment a fourth criterion is added, where SUMPRODUCT only needs its two ranges widened. The absolute reference on the weights row is what makes filling down safe: without it, Summit Fabrication's formula would slide two rows further from the weights and start multiplying its scores by whatever happens to sit there instead.