How to Count Unique Values in Excel — The Modern Way and the Classic Formula

TL;DR — Excel has no COUNTUNIQUE function, so "how many different values are here?" has two answers. Modern (365/2021): =COUNTA(UNIQUE(range)) — spill the distinct values, then count them. Classic (older versions): =SUMPRODUCT(1/COUNTIF(range, range)) — a clever array trick. Both have one trap in common: a blank cell throws the count off, and each fails differently.

=COUNTA(UNIQUE(A2:A100))                          ' modern: distinct count
=COUNTA(UNIQUE(FILTER(A2:A100, A2:A100<>"")))     ' modern, blanks excluded
=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100))          ' classic (breaks on blanks)
=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100, A2:A100&""))  ' classic, blank-safe

"Count the distinct values" is one of the most common real tasks in Excel and one of the few with no dedicated function — which is exactly why it's worth understanding why each approach works, not just pasting it.

What you'll learn

Why there's no single "count unique" function — and the two eras of answers
The modern COUNTA(UNIQUE()) pattern, and its blank-cell trap
The classic SUMPRODUCT(1/COUNTIF()) trick — and why it works
How to keep blanks from silently adding 1 to your count
"Distinct values" vs "values that appear exactly once" — a common mix-up

The mental model: there is no COUNTUNIQUE — you compose one

The reason this task feels awkward is that you're looking for a function that doesn't exist. Excel gives you pieces instead, and you assemble the answer. There are two assemblies, and which you use depends entirely on your Excel version:

Modern (365, 2021): get the distinct list, then count it. UNIQUE returns the different values as a spilled array; COUNTA counts how many came back. Readable, and it recalculates live as data changes.
Classic (2019 and earlier): no UNIQUE, so you use a single array formula, SUMPRODUCT(1/COUNTIF(range,range)), that counts distinct values without ever listing them.

If you're on a modern version, use the modern pattern — it's clearer and you can see the distinct values spill out, which makes debugging trivial. Reach for the classic formula only when you must support an older file.

The modern way — and the blank that adds 1

=COUNTA(UNIQUE(A2:A100))

UNIQUE spills the distinct values; COUNTA counts them. Clean — until there's an empty cell in the range. UNIQUE treats blank as a value: it returns a 0 (its representation of an empty cell) as one of the distinct entries, so your count comes back one higher than the number of real values. This is the number-one reason a unique count is "off by one."

The fix is to strip blanks before counting, with FILTER:

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

FILTER keeps only non-empty cells, UNIQUE de-dupes them, COUNTA counts. This nested form — filter, de-dupe, count — is the one to memorize, because real columns almost always have trailing blanks. If FILTER finds nothing it returns #CALC!, so add FILTER(..., ..., "") as an empty fallback if the range can be entirely empty.

The classic way — and why the trick actually works

On older versions, the canonical formula is:

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

It looks like magic, but the logic is worth understanding because it's the key to fixing its bug. COUNTIF(range, range) returns an array — for each cell, how many times its value appears in the range. If "West" appears 4 times, every one of those four cells produces a 4. Take 1/that, and each "West" cell contributes 1/4. Four cells × ¼ = 1. Every distinct value, however many times it repeats, sums to exactly 1 — so the grand total is the number of distinct values. Understand that and you never have to memorize it.

The catch: if any cell is blank, COUNTIF returns 0 for it, you get 1/0, and the whole thing collapses to #DIV/0!. The blank-safe version neutralizes both the division and the blank's contribution:

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

The &"" makes COUNTIF count blanks as a matched empty string (so no zero divisor), and (A2:A100<>"") puts a 0 in the numerator for blank cells so they add nothing. It's the same idea as the modern FILTER fix, expressed in the older array vocabulary. (For more on this array-summing pattern, see SUMPRODUCT.)

Distinct values vs values that appear only once

These sound the same and aren't — mixing them up is the most common silent wrong answer in this whole topic:

Distinct count — how many different values exist. {A, A, B} → 2. That's everything above.
Count of values appearing exactly once — how many values are not repeated. {A, A, B} → 1 (only B). Different question, different formula:

=SUMPRODUCT(--(COUNTIF(A2:A100, A2:A100)=1))   ' values that appear exactly once
=ROWS(UNIQUE(A2:A100, , TRUE))                 ' modern: UNIQUE's third argument

UNIQUE's third argument (exactly_once = TRUE) returns only the values that appear a single time — not the distinct list. If you want "how many different customers," that's the default UNIQUE; if you want "how many customers ordered only once," that's exactly_once. Reach for the wrong one and your count is plausibly, silently wrong.

The judgment call

On a modern Excel, the answer is always =COUNTA(UNIQUE(FILTER(range, range<>""))) — filter blanks, de-dupe, count — because it's readable, self-debugging (you can see the spill), and blank-safe. Keep the SUMPRODUCT(1/COUNTIF()) trick in your pocket only for legacy files, and always in its &"" blank-safe form. Above all, be sure which question you're answering: "how many different values" and "how many values appear once" look identical in a sentence and produce different numbers on the sheet.

How ExcelMaster helps

Distinct-count formulas are easy to get subtly wrong — an off-by-one from a trailing blank, a #DIV/0! from the classic trick, or the distinct-vs-once mix-up that no error ever flags. ExcelMaster checks your Excel version, picks the modern COUNTA(UNIQUE(FILTER(...))) or the blank-safe classic formula accordingly, and — crucially — confirms which question you mean before it writes anything: distinct values, or values that occur exactly once. Say "count the distinct customers, ignoring blanks," and it returns a formula that's right for your version and your intent.

Frequently asked questions

How do I count unique values in Excel?

On Excel 365 or 2021, use =COUNTA(UNIQUE(range)), or =COUNTA(UNIQUE(FILTER(range, range<>""))) to ignore blank cells. On older versions, use the array formula =SUMPRODUCT((range<>"")/COUNTIF(range, range&"")), which counts distinct values without listing them.

Is there a COUNTUNIQUE function in Excel?

No. Excel has no single "count unique" function. You compose one: UNIQUE + COUNTA on modern versions, or SUMPRODUCT(1/COUNTIF(...)) on older ones. A PivotTable can also show a "Distinct Count" for one-off analysis.

How do I count unique values in older Excel without UNIQUE?

Use =SUMPRODUCT(1/COUNTIF(range, range)). Each value that appears n times contributes n × (1/n) = 1, so the total is the number of distinct values. If the range has blanks, use the blank-safe form =SUMPRODUCT((range<>"")/COUNTIF(range, range&"")) to avoid a #DIV/0! error.

Why is my unique count one too high?

A blank cell in the range is being counted as a value. UNIQUE returns an empty cell as 0, adding 1 to the distinct count. Exclude blanks with =COUNTA(UNIQUE(FILTER(range, range<>""))).

What's the difference between counting distinct values and values that appear once?

Distinct count treats {A, A, B} as 2 (two different values). Counting values that appear exactly once treats it as 1 (only B is non-repeated). Use default UNIQUE for distinct; use UNIQUE(range,,TRUE) or SUMPRODUCT(--(COUNTIF(range,range)=1)) for values occurring once.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-07.