Borda count in Excel: points by rank with SUMPRODUCT

How the Borda count works

With n options, a 1st place earns n − 1 points, a 2nd place n − 2, and so on down to 0 for the last place. Some versions give n points to the 1st place and 1 to the last. The ranking of the options is the same either way, because every voter gets the same constant extra.

Each voter hands out 3 + 2 + 1 + 0 = 6 points, so 100 voters hand out 600 points in total. That total is the first check of any Borda sheet.

Step 1: the preference profile

Ballots with the same order are grouped into one row with the number of voters who cast it: a preference profile. Here five ballot types cover all 100 voters: 34 rank Zoo, Museum, Theatre, Beach, 28 rank Beach, Museum, Zoo, Theatre, and so on. If you have one row per ballot instead, put 1 in the Voters column of every row, and the same formulas work.

Step 2: count each option by position with SUMIF

How many voters put Museum 2nd? Add the voter counts of the rows where the 2nd column says Museum:

=SUMIF(C$2:C$6,$A12,$A$2:$A$6)

Copy it across and down the table B12:E15. The criteria range moves with the position column (B for 1st, C for 2nd …), $A12 always reads the option in column A and the voter counts stay fixed. Each row of the table adds up to 100, every voter ranks every option once. Museum is ranked 1st by 18 voters but 2nd by 74.

Step 3: Borda scores with SUMPRODUCT

Multiply each position count by its points and add up: =SUMPRODUCT(B12:E12,$B$9:$E$9). Museum scores 18 × 3 + 74 × 2 + 8 × 1 + 0 × 0 = 210, Zoo 158, Beach 126 and Theatre 106. The total is 600, as it should be. See SUMPRODUCT with multiple criteria for more on the function.

Without the helper table, one formula does it: =SUMPRODUCT((B2:E6=A12)*B9:E9*A2:A6). The comparison marks where the option appears, the points row gives each position its weight and the voter column counts each ballot type. The winner is =INDEX(A12:A15,MATCH(MAX(F12:F15),F12:F15,0)), and points per voter, 210 ÷ 100 = 2.10, makes elections of different sizes comparable.

Worked example: 100 ranked ballots, Borda vs plurality

Rows 2 to 6 are the preference profile (voter counts in yellow). Row 9 holds the points per position. Rows 12 to 15 count each option by position and calculate its Borda score, and rows 18 to 22 name the winners.

Worked example: 100 ranked ballots, Borda vs plurality

	A	B	C	D	E	F
1	Voters	1st	2nd	3rd	4th
2	34	Zoo	Museum	Theatre	Beach
3	28	Beach	Museum	Zoo	Theatre
4	18	Museum	Theatre	Beach	Zoo
5	12	Theatre	Museum	Zoo	Beach
6	8	Beach	Zoo	Museum	Theatre
7	100	voters in total
8
9	Points	3	2	1	0
10
11	Option	1st	2nd	3rd	4th	Borda score
12	Museum	18	74	8	0	210
13	Zoo	34	8	40	18	158
14	Beach	36	0	18	46	126
15	Theatre	12	18	34	36	106
16	Total points (6 per voter)					600
17
18	Borda winner					Museum
19	Plurality winner (most 1st places)					Beach
20	Plurality winner's share of 1st places					36.0%
21	Museum score in one step (no helper table)					210
22	Average points per voter, Museum					2.10

Fictional ballots. Educational only: the guide explains how counting methods work and does not recommend any electoral system.

Borda vs plurality, runoff and Condorcet

Plurality only looks at first places: Beach wins with 36%, short of a majority. Borda rewards broad support: Museum is the favourite of only 18 voters, but it is the second choice of 74 of them. Museum also beats every other option head to head (58 to 42 against Zoo, 64 to 36 against Beach), so it is the Condorcet winner here.

Instant runoff gives yet another answer on the same ballots. Theatre is eliminated first and its 12 votes go to Museum (30). Then Museum, now last, is eliminated, and Beach beats Zoo 54 to 46. Three counting rules, two different winners. Which rule is fairer is a matter of values, so the choice of method should be agreed before the vote, never after.

Weaknesses of the Borda count

Borda is sensitive to strategic voting: a voter can bury a strong rival at the bottom of the ballot to cost it points. It also depends on irrelevant alternatives: adding or removing a weak option can change the order of the others. It works well for committees, awards, rankings and surveys where honest rankings are expected, and the results are easy to explain.

Common mistakes

• Points in the wrong order. 0 for 1st and 3 for last reverses the result. The 1st place must earn the most points.
• Relative criteria ranges. Copying =SUMIF(B2:B6,A12,A2:A6) down shifts the ranges below the data. Lock the rows (B$2:B$6) and the voter column ($A$2:$A$6).
• Counting ballots instead of voters. COUNTIF counts the five ballot types, not the 100 voters. Use SUMIF with the voter column.
• Incomplete ballots. If voters may leave options unranked, decide the rule first (for example 0 points for unranked options) and check the total.
• Skipping the total check. All scores must add up to points per ballot × voters, 600 here. A different total means a misspelled option or a missing row.
• Ties ignored. INDEX/MATCH returns the first maximum only. Count the maximum with COUNTIF and agree a tie-break before the count.