How to count unique values in Excel
Counting rows tells you how many entries there are. Counting unique values tells you how many different ones, such as different customers rather than orders.
UNIQUE removes the repeats, and COUNTA counts what’s left.
The formula
| A | |
|---|---|
| 1 | Customer |
| 2 | Larkspur |
| 3 | Birch |
| 4 | Larkspur |
| 5 | Orchard |
| 6 | Birch |
=COUNTA(UNIQUE(A2:A6))Result: 3
Five orders, but only three different customers.
Step by step
- Type =COUNTA(UNIQUE( and select the list.
- Close both brackets and press Enter.
Other ways to write it
=SUMPRODUCT(1/COUNTIF(A2:A6,A2:A6))The classic trick for versions of Excel without UNIQUE. It fails if the range has blank cells.
Each of these gives the same result on the example above, in Excel and in Cellwise.
Common mistakes
- Counting every row
- COUNTA on its own counts duplicates too.
- Blanks in the list
- UNIQUE counts a blank as a value. Filter blanks out first if the list has gaps.
Reading isn’t the same as doing
Finding duplicates gives you real tasks on this, and tests your formulas on data you haven’t seen. It’s free, with no sign-up.