How to calculate a weighted score in Excel

The weighted score with SUMPRODUCT

The sheet is a software selection scorecard: five criteria down the side, three vendors across the top, each scored 1 to 5 by the team. The weights in column B say how much each criterion matters. Price is 30% of the decision, ease of use only 10%.

For Ledgerly that is 4 x 30% + 3 x 25% + 4 x 15% + 3 x 20% + 5 x 10% = 3.65. Put the formula in C7 and fill it right to E7. The weights range stays put because of the $ signs, and the scores range moves to each vendor's column.

The same layout works for any weighted scorecard: a marking rubric (criteria down the side, students across), supplier reviews, job candidates or project prioritisation. To show a score as a percentage of the best possible, divide by the top of the scale: =C7/5 gives 73%.

Make the weights add up to 100%

If the weights add up to 100%, the weighted score stays on the same 1-5 scale as the scores, which makes it easy to read. Put =SUM(B2:B6) under the weights and keep an eye on it whenever someone changes one. For a visible check, use =IF(ROUND(SUM(B2:B6),6)=1,"OK","Weights add to "&TEXT(SUM(B2:B6),"0%")). The ROUND matters with weights like 33.3%, which never add to exactly 100%.

It is often easier to agree how important each criterion is than to agree on percentages. Column G rates importance from 1 to 10. Divide by the sum of those ratings and the result is a proper weighted score again: Northbook gets 3.77 instead of 3.75. To see the percentage each rating works out to, use =G2/SUM($G$2:$G$6) for each row; Price comes out at 29%.

If you type weights as 30, 25, 15 instead of 30%, SUMPRODUCT alone gives 365 instead of 3.65. Dividing by SUM of the weights fixes that too, which is why /SUM(weights) is a safe habit.

Pick the winner and rank the options

With the weighted scores in row 7, MAX finds the top score and INDEX and MATCH return the name from the header row. In Excel 365 or 2021 =XLOOKUP(MAX(C7:E7),C7:E7,C1:E1) does the same.

To rank every option, use =RANK.EQ(C7,$C$7:$E$7) under each one: Ledgerly is 2nd and ClearSuite 3rd. If two options tie, both formulas pick the first one in the row, so look at the scores, not just the name.

Before you announce the winner, look at the gap. Northbook beats Ledgerly by 0.1 on a 5-point scale. Change the price weight from 30% to 35% (taking 5 points from features) and the order flips: Ledgerly 3.70, Northbook 3.60. A result that close is a tie; say so, and let the team decide on the criteria that matter most.

Missing scores change the winner

ClearSuite's support score is not in yet, so E4 is blank. SUMPRODUCT counts the blank as 0, which is the worst possible score, and ClearSuite comes last with 3.45. Rescore only the criteria it has been scored on, by dividing by the weights of those criteria (85% here), and it jumps to 4.06, ahead of everyone.

Neither number is the right one. The first punishes ClearSuite for missing data; the second gives it a free pass on support. Use the second formula as a "provisional" score, and show the gap: =COUNTBLANK(C2:E6) tells you how many scores are still missing. The real fix is to get the missing score before you decide.

Use ISNUMBER, not <>"", in the divisor. If someone types n/a in the cell, <>"" counts it as scored, while ISNUMBER does not. SUMPRODUCT with commas treats text as 0, but the version with * between the ranges returns #VALUE! on text.

Put cost and other measures on the same scale

Every criterion must use the same scale and the same direction, higher is better, or the weights mean nothing. Annual cost in row 9 is in dollars, and lower is better. Adding 12,400 into a 1-5 scorecard would swamp everything else.

Convert it to a score: the cheapest option gets 5 and the others get a share of 5 in proportion, cheapest cost divided by their cost. ClearSuite at 9,800 gets 5, Ledgerly 3.95 and Northbook 2.59. The other common method, =1+4*(MAX($C$9:$E$9)-C9)/(MAX($C$9:$E$9)-MIN($C$9:$E$9)), always gives the most expensive option a 1, even if it is only slightly more expensive. That exaggerates small differences, so prefer the ratio when the costs are close.

For a score that is already "higher is better" but on another scale, divide by its maximum and multiply by 5. 80 out of 100 becomes 4.

Minimum scores: when a weighted total is not enough

Weighted scores let strength in one area make up for weakness in another. That is usually the point, but not always. A vendor that scores 5 on features and 1 on security can still win, and you probably do not want it to.

Set a minimum for each criterion in column H and flag any option that falls below one. The comparison returns TRUE for each criterion below its minimum, -- turns those into 1s, and SUMPRODUCT counts them. Northbook's price score of 2 is below the minimum of 3, so its 3.75 is replaced by a flag. Ledgerly passes every minimum and keeps its 3.65. ClearSuite is flagged too, because its blank support score counts as 0.

Which method to pick

For a standard scorecard: weights adding to 100% and =SUMPRODUCT(weights,scores) per option, with the weights locked. If people rate importance instead of giving percentages, divide by SUM of the ratings. Convert costs and other measures to the same "higher is better" scale before they enter the scorecard. Treat blanks on purpose with ISNUMBER, and add minimum scores for anything that is a deal-breaker. For averaging grades with weights, the weighted average and gradebook guides cover the classroom version.