Count Unique Values

Excel Formulas › Count

Excel 365Excel 2021+Legacy alt included

Counting how many distinct values appear in a list — unique customers, unique products, unique visitors — is trivial in modern Excel with COUNTA(UNIQUE(…)), and still doable in any version with a classic SUMPRODUCT/COUNTIF trick.

Quick formula: to count the distinct entries in A2:A10:
=COUNTA(UNIQUE(A2:A10))
UNIQUE returns the de-duplicated list; COUNTA counts how many items came back.

Functions used (tap for the full reference guide):

UNIQUECOUNTASUMPRODUCTCOUNTIF

The example

A list of orders with the customer on each row. Five names appear, but only three are distinct.

AB
1CustomerAmount
2Acme$120
3Bolt$80
4Acme$200
5Cedar$50
6Bolt$300
7Distinct customers:3

The formula

The count of distinct customers is:

=COUNTA(UNIQUE(A2:A6)) // Acme, Bolt, Cedar → 3

How it works

Two functions working as a pair:

  1. UNIQUE(A2:A6) returns each customer once: Acme, Bolt, Cedar — spilling into three cells.
  2. COUNTA counts the non-empty items in that spilled list — 3.
  3. Wrap the list in FILTER first to count distinct values that also meet a condition (see Variations).

Try it: interactive demo

Live demo

Type a comma-separated list. The unique count and both formulas update as you type.

Distinct values:

Variations

Count distinct values that meet a condition

Distinct customers in the West region only — filter first, then de-duplicate:

=COUNTA(UNIQUE(FILTER(A2:A6, C2:C6="West")))

Legacy version (any Excel, no UNIQUE)

The classic array trick — divide 1 by each value’s count and sum the pieces:

=SUMPRODUCT(1/COUNTIF(A2:A6, A2:A6)) // each duplicate group sums to 1

Ignore blanks in the legacy formula

Blanks cause a divide-by-zero. Guard them:

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

Pitfalls & errors

UNIQUE counts blanks as a value. An empty cell in the range shows up as one "unique" blank. Filter them out: =COUNTA(UNIQUE(FILTER(A2:A6, A2:A6<>""))).

#DIV/0! in the SUMPRODUCT version. A blank cell makes COUNTIF return 0 and the division fails. Use the blank-guarded form shown above.

"Unique" vs "distinct". UNIQUE(range, , TRUE) returns values that appear exactly once (true uniques); the default returns each value once (distinct). For a normal "how many different" count, use the default.

Practice workbook

📊
Download the free Count Unique Values practice workbook
The order list with live COUNTA(UNIQUE()), the conditional FILTER version, and the legacy SUMPRODUCT trick, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I count unique values in Excel without UNIQUE?
Use =SUMPRODUCT(1/COUNTIF(range, range)). It divides 1 by how many times each value occurs, so every duplicate group sums to exactly 1, giving the distinct count. Guard against blanks to avoid #DIV/0!.
What's the difference between unique and distinct?
Distinct means each different value counted once (Acme, Bolt, Cedar = 3). Unique (in the strict sense) means values appearing exactly once. UNIQUE's third argument set to TRUE returns the strict uniques.
How do I count distinct values with a condition?
Wrap the range in FILTER first: =COUNTA(UNIQUE(FILTER(A2:A100, B2:B100="West"))) counts distinct customers in the West region.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Unique sorted list · COUNTIFS with multiple criteria · Flag duplicates

Function references: UNIQUE · COUNTA · SUMPRODUCT · COUNTIF