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
| A | B | C | |
|---|---|---|---|
| 1 | City | ||
| 2 | Lisbon | 3 | |
| 3 | Porto | ||
| 4 | Lisbon | ||
| 5 | Faro | ||
| 6 | Porto | ||
| 7 | Lisbon |
In C2: =COUNTA(UNIQUE(A2:A7)) → 3
Lisbon, Porto and Faro.
How it works
- UNIQUE returns the values of
A2:A7with every repeat removed. - COUNTA counts how many values are left.
- Nothing has to be sorted first, and the count updates when the list changes.
Variations
In older versions of Excel
A B C 1 City 2 Lisbon 3 3 Porto 4 Lisbon 5 Faro 6 Porto 7 Lisbon In C2:
=SUMPRODUCT(1/COUNTIF(A2:A7, A2:A7))→ 3Each 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.
See the list itself
A B C 1 City 2 Lisbon Lisbon 3 Porto Porto 4 Lisbon Faro 5 Faro 6 Porto 7 Lisbon In C2:
=UNIQUE(A2:A7)→ Lisbon; Porto; FaroThe result spills into the cells below.
Values that appear exactly once
A B C 1 City 2 Lisbon 1 3 Porto 4 Lisbon 5 Faro 6 Porto 7 Lisbon In C2:
=COUNTA(UNIQUE(A2:A7, FALSE, TRUE))→ 1The third argument keeps only values with no repeat: here, Faro.
Watch out for
- Empty cells. UNIQUE treats an empty cell as a value of its own, so a range with gaps counts one too many. Keep the range to the filled rows, or filter the blanks out:
=COUNTA(UNIQUE(FILTER(A2:A7, A2:A7<>""))). - Spaces and spelling. "Lisbon" and "Lisbon " with a space at the end count as two values. Clean the data with TRIM first. Upper and lower case do not matter.
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
- UNIQUE: Removes duplicates from a list. The result spills.
- COUNTA: Counts cells that are not empty.
- SUMPRODUCT: Multiplies matching items in arrays, then adds the results.
- COUNTIF: Counts the cells that meet a condition.
- FILTER: Keeps only the rows that meet a condition. The result spills.