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.

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.

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