How to / Counting

How to count unique values in Excel

In Excel 2021 and Microsoft 365, use =COUNTA(UNIQUE(A2:A7)). UNIQUE lists each value once and COUNTA counts the list.

How many different cities are in the list

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

In C2: =COUNTA(UNIQUE(A2:A7)) → 3

Lisbon, Porto and Faro.

How it works

  1. UNIQUE returns the values of A2:A7 with every repeat removed.
  2. COUNTA counts how many values are left.
  3. Nothing has to be sorted first, and the count updates when the list changes.

Variations

  1. In older versions of Excel

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

    In C2: =SUMPRODUCT(1/COUNTIF(A2:A7, A2:A7)) → 3

    Each value counts as 1 divided by the number of times it appears, so a city listed three times adds three thirds. The range must have no empty cells.

  2. See the list itself

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

    In C2: =UNIQUE(A2:A7) → Lisbon; Porto; Faro

    The result spills into the cells below.

  3. Values that appear exactly once

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

    In C2: =COUNTA(UNIQUE(A2:A7, FALSE, TRUE)) → 1

    The third argument keeps only values with no repeat: here, Faro.

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