How do I count unique values in a column in Excel?

General Discussions
1

I have a column of data in an Excel .xls file, and I want to count the number of unique values in that column.

Most formulas I’ve found online give me errors. What’s a simple and reliable way to count unique values, either using formulas or Excel features like pivot tables?

2

When I need to count unique values, I usually go for an array formula like:

=SUM(1/COUNTIF(A2:A100, A2:A100))

Just make sure to press CTRL+SHIFT+ENTER (not just Enter) if you’re using an older Excel version. In Excel 365, you can skip that part, dynamic arrays handle it automatically.

3

Honestly, I just use a Pivot Table. Drop the column into “Rows,” and then again into “Values” set to “Count.”

This way, I see the count of each unique value, and the number of rows tells me how many unique values there are.

Super visual and no formulas needed.

4

If you’re using Excel 365 or Excel 2019, just do:

=COUNTA(UNIQUE(A2:A100))

That’s by far the cleanest way. UNIQUE() pulls out the distinct values, and COUNTA() counts how many there are.

Way less messy than the older array tricks.