How to count unique values with UNIQUE and COUNTA

This counts how many different names are in a column, rather than how many rows. Repeats are counted once.

The formula

=COUNTA(UNIQUE(A:A)) in column C

What each part does

UNIQUE: Strips the list down to one of each value.
COUNTA: Counts what is left.

How it works, and what to watch for

UNIQUE gathers one of each, and COUNTA counts what is left. Together they answer "how many different", not "how many rows".

UNIQUE needs Microsoft 365. On older Excel the same count comes from a SUMPRODUCT with COUNTIF, which the version switch will give you.

Blank cells can sneak in as one of the "unique" values, so trim the range to the rows that actually hold data if the count looks one too high.

Questions

How do I list the different values, not just count them?

Use UNIQUE on its own and it spills the distinct list down the column. Wrap it in SORT to put them in order.

No UNIQUE on my Excel, what now?

The classic version is =SUMPRODUCT(1/COUNTIF(range,range)), which counts the distinct values without needing UNIQUE.

The example sheet

Customer	Different customers
Acme
Bellrose
Acme
Larkspur

If this helped, these are close by

Count rows that meet two conditions — How many, with more than one rule
Check a name against another list — Is this one already on the list?
Find who unfollowed you — Compare last month's list with today's

When this formula goes wrong

#SPILL! and the cell that is in the way — A dynamic formula with no room

More on UNIQUE, and every walkthrough that uses it.

Formula Steps Watch how Excel formulas are built. Download the sheet.

Settings: separator, Excel version

Argument separator

Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.

Excel version

XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.