How to count ties for the highest value in Excel
Formula (Excel and Google Sheets)
=COUNTIF(B2:B11,MAX(B2:B11))Count how many cells equal the maximum: =COUNTIF(B2:B11,MAX(B2:B11)).
- Result on the example
- 3 in
E1 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.
How it works
MAX finds the top score, 97, and COUNTIF counts the cells equal to it. Three students tied for first.
Because MAX is inside the formula, the count updates when any score changes.
=COUNTIF(B2:B11,MAX(B2:B11))In Google Sheets
The same formula works in Google Sheets: =COUNTIF(B2:B11,MAX(B2:B11)). We ran it in Google Sheets mode on the same data and it returns 3.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(--(B2:B11=MAX(B2:B11))) | 3 | The same count as a comparison. |
| =COUNTIF(B2:B11,MIN(B2:B11)) | 1Different result | Counts ties for the lowest value. Counts the 1 student with the lowest score. |
Common mistakes
- Scores with hidden decimals (96.99 displayed as 97) are not equal to 97. Round the scores, or compare with ROUND on both sides.
- RANK gives tied values the same rank, so two students ranked 1 means the next is ranked 3. Count ties first if you award places.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Count ties for the highest value
E1, count how many students share the highest score in B2:B11.Questions
How do I list the names of the top scorers?
In Excel 365 or Google Sheets: =FILTER(A2:A11,B2:B11=MAX(B2:B11)).
How do I count ties for the second-highest value?
Find the second-highest distinct value first, in Excel 365 or Google Sheets: =COUNTIF(B2:B11,MAX(FILTER(B2:B11,B2:B11<MAX(B2:B11)))).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.