Sum, Count & Average

Excel formula for Count unique values

Count how many distinct values are in a range using UNIQUE + COUNTA.

Copy this formula

=COUNTA(UNIQUE(A2:A100))

How the formula works

UNIQUE returns the distinct values from a range, and COUNTA counts how many there are. This works in Excel 365 and Excel 2021. In older versions, use a SUMPRODUCT/COUNTIF combination.

Examples

=COUNTA(UNIQUE(B2:B500))→37

There are 37 distinct customers in the list.

=COUNTA(UNIQUE(FILTER(A2:A100, A2:A100<>"")))→24

Ignores blank cells, which UNIQUE would otherwise count as one value.

=SUM(--(COUNTIF(A2:A100, A2:A100)=1))→9

Counts only the values that appear exactly once.

Other ways to do this

Legacy (pre-365)

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

Works without UNIQUE; the range must not contain blanks.

FAQ

Why does the legacy formula return an error?

Blank cells cause a divide-by-zero. Restrict the range to filled cells or wrap COUNTIF criteria to skip blanks.

What is the difference between unique and distinct?

A distinct count takes each different value once. Strictly, unique means values that appear only once. =COUNTA(UNIQUE(range)) gives the distinct count; UNIQUE(range, , TRUE) keeps only values that occur exactly once.

Which Excel versions have UNIQUE?

Excel for Microsoft 365, Excel 2021 and later, and Excel for the web. Earlier versions need the SUMPRODUCT/COUNTIF formula.

How do I count unique values that meet a condition?

Filter first: =COUNTA(UNIQUE(FILTER(A2:A100, B2:B100="East"))).

Related formulas

Count cells that meet a condition Get a list of values without duplicates

Functions used

UNIQUE COUNTA COUNTIF

Need a formula for your own data?

Describe what you want in plain language and let ExcelGPT write, explain, and verify the formula — free to start, no plugin.

Try ExcelGPT free