Count Unique Values in Excel: 3 formulas for any version

A column has 500 orders. How many different customers placed them? That is a count of unique values.

Excel 365 / 2021

=COUNTA(UNIQUE(A2:A500))

UNIQUE lists each name once, COUNTA counts the list. Simple and fast.

Ignore blank cells

=COUNTA(UNIQUE(FILTER(A2:A500,A2:A500<>"")))

With a condition

Different customers in North (region in B):

=COUNTA(UNIQUE(FILTER(A2:A500,B2:B500="North")))

Older Excel (2016 and before)

=SUMPRODUCT(1/COUNTIF(A2:A500,A2:A500))

How it works: a name appearing 4 times gets 1/4 on each of its 4 rows, which adds back to 1. Every name contributes exactly 1.

It breaks on blank cells (divide by zero). Blank-safe version:

=SUMPRODUCT((A2:A500<>"")/COUNTIF(A2:A500,A2:A500&""))

Slow above a few thousand rows.

No formula: pivot table

Insert › PivotTable › tick Add this data to the Data Model › put the field in Values › Value Field Settings › Distinct Count. See pivot table count unique.

Want the list, not the count?

See unique values. Want to see which ones repeat? find duplicates.