# How to count ties for the highest value in Excel

Count how many cells equal the maximum: `=COUNTIF(B2:B11,MAX(B2:B11))`.

**Excel:** `=COUNTIF(B2:B11,MAX(B2:B11))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Student | Score |  | Top scorers |
| Ava | 92 |  |  |
| Ben | 88 |  |  |
| Chloe | 97 |  |  |
| Dan | 75 |  |  |
| Eve | 97 |  |  |
| Finn | 81 |  |  |
| Gia | 97 |  |  |
| Hugo | 90 |  |  |
| Iris | 68 |  |  |
| Jack | 85 |  |  |

Formula in E1: `=COUNTIF(B2:B11,MAX(B2:B11))`

Result: 3

## 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.

## Other ways

- `=SUMPRODUCT(--(B2:B11=MAX(B2:B11)))`: The same count as a comparison.
- `=COUNTIF(B2:B11,MIN(B2:B11))`: 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.

## 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))))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-ties-for-the-highest-value