Weighted vendor evaluation scorecard (SUMPRODUCT decision matrix)

Normalize criterion weights automatically so edits never skew totals

In procurement RFP matrices, stakeholders often tweak criterion weights (e.g., raising Security from 30% to 40%) and accidentally leave total weights at 110%.

Dividing SUMPRODUCT($B$2:$B$5, C2:C5) by SUM($B$2:$B$5) normalizes weights automatically—whether entered as percentages (0.40, 0.30, 0.20, 0.10) or raw 1–10 importance points.

Vendor weighted score and winner comparison

Vendor A scores 31.5 + 28.0 + 17.0 + 7.0 = 83.50; Vendor B scores 26.25 + 33.25 + 18.0 + 8.5 = 86.00.

=ROUND(SUMPRODUCT($B$2:$B$5, D2:D5) / SUM($B$2:$B$5), 2)

Verify weights sum to 100%

Display an explicit audit check next to the weight column header.

=ROUND(SUM(B2:B5), 4) = 1

Inputs and interpretation

Use nonnegative weights with a positive total and complete numeric scores on the same higher-is-better scale. Price is a score here, not a raw cost. Normalization preserves relative weights but changing weights still changes tradeoffs. The 100% check applies only to percentage weights, not raw importance points. The winner formula awards an exact tie to VendorA; define any tie or mandatory-criteria policy separately.

Further reading

• Microsoft Support — SUMPRODUCT function