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

ABC
1City
2LisbonLisbon
3Porto
4Lisbon
5Faro
6Porto
7Lisbon

In C2: =INDEX(A2:A7, MATCH(MAX(COUNTIF(A2:A7, A2:A7)), COUNTIF(A2:A7, A2:A7), 0)) → Lisbon

How it works

  1. COUNTIF(A2:A7, A2:A7) counts, for every cell, how many times its value appears: 3, 2, 3, 1, 2, 3.
  2. MAX picks the largest of those counts, 3.
  3. MATCH finds the first row with that count, and INDEX returns the city on that row.

Variations

  1. How many times it appears

    ABC
    1City
    2Lisbon3
    3Porto
    4Lisbon
    5Faro
    6Porto
    7Lisbon

    In C2: =MAX(COUNTIF(A2:A7, A2:A7)) → 3

  2. The most common number

    ABC
    1Size
    23838
    340
    438
    542
    638

    In C2: =MODE(A2:A6) → 38

    MODE only works on numbers. On text it returns #N/A.

Watch out for

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

Practise it

Related