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.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
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 teachesMay contain 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.