How to / Counting
How to find the most common value in Excel
For text, use =INDEX(A2:A7, MATCH(MAX(COUNTIF(A2:A7, A2:A7)), COUNTIF(A2:A7, A2:A7), 0)). For numbers, =MODE(A2:A7) is enough.
The city that appears most often
| A | B | C | |
|---|---|---|---|
| 1 | City | ||
| 2 | Lisbon | Lisbon | |
| 3 | Porto | ||
| 4 | Lisbon | ||
| 5 | Faro | ||
| 6 | Porto | ||
| 7 | Lisbon |
In C2: =INDEX(A2:A7, MATCH(MAX(COUNTIF(A2:A7, A2:A7)), COUNTIF(A2:A7, A2:A7), 0)) → Lisbon
How it works
COUNTIF(A2:A7, A2:A7)counts, for every cell, how many times its value appears: 3, 2, 3, 1, 2, 3.- MAX picks the largest of those counts, 3.
- MATCH finds the first row with that count, and INDEX returns the city on that row.
Variations
How many times it appears
A B C 1 City 2 Lisbon 3 3 Porto 4 Lisbon 5 Faro 6 Porto 7 Lisbon In C2:
=MAX(COUNTIF(A2:A7, A2:A7))→ 3The most common number
A B C 1 Size 2 38 38 3 40 4 38 5 42 6 38 In C2:
=MODE(A2:A6)→ 38MODE only works on numbers. On text it returns #N/A.
Watch out for
- A tie. When two values share first place, the formula returns the one that comes first in the list.
- Older versions of Excel. In Excel 2019 and earlier, finish the formula with Ctrl+Shift+Enter, not Enter alone, so that Excel works through the whole range.
Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.
Functions used
- INDEX: The value at a given row and column of a range.
- MATCH: The position of a value in a row or column.
- COUNTIF: Counts the cells that meet a condition.
- MAX: The largest number.
- MODE: The most frequently occurring number.