Count the unique values in a range
Count how many distinct values appear in a range with one formula in current Excel and Sheets, plus the array formula that covers older Excel.
Last updated
Fill in the blanks
Assembled in your browser — nothing you type is stored or sent anywhere.
Your formula
Works in both Excel and Google Sheets unless the fine print below says otherwise.
Why it's built this way
- →UNIQUE spills out a list containing each distinct value in the range exactly once; COUNTA then counts how many cells that spilled list occupies — two simple functions standing in for what used to take an array formula.
- →Because UNIQUE recalculates its spill automatically, the count updates on its own as rows are added to the range — no need to rewrite the formula or extend a helper column.
The fine print
- →UNIQUE requires Excel 2021/365 or Google Sheets. Older perpetual Excel (2019 and earlier) doesn't have it — SUMPRODUCT(1/COUNTIF(range, range)) is the classic substitute there, though it errors on any blank cell in the range.
- →A blank cell inside the range counts as one unique value under this formula — filter blanks out first if that's not the intended count.
- →UNIQUE is a dynamic array formula; pointing it at a full-column reference spills over far more rows than intended, so bound the range instead.
Part by part
- =COUNTA(
- Counts non-empty cells. Applied to the result of UNIQUE, it counts how many distinct values came back rather than how many rows went in.
- UNIQUE({{range}})
- Returns each distinct value once, as a spilled array. It is a modern dynamic-array function, which is what makes this two-function form possible.
Common mistakes
=COUNT(UNIQUE(A2:A100))COUNT tallies numbers only, so a column of text names returns zero. COUNTA counts anything non-empty, which is what you want for distinct values of any type.
=COUNTA(UNIQUE(A2:A100))=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100))The pre-dynamic-array classic, and it returns #DIV/0! the moment the range contains a blank cell, because the count for that entry is zero. It also recalculates slowly on large ranges.
=COUNTA(UNIQUE(A:A))A whole-column reference makes UNIQUE return one entry for the blank rows as well, so the count is one higher than expected. Bound the range to the data.
Questions
Does it count blanks as a unique value?
UNIQUE includes an empty entry when the range contains blanks, and COUNTA then ignores it — so the count is usually right. Using a whole-column reference is the case where it is not.
Is it case-sensitive?
No. "East" and "east" are treated as the same value, which is normally what you want. Case-sensitive counting needs an EXACT-based approach and is considerably slower.
What if UNIQUE is not available?
Excel 2019 and earlier lack it. The SUMPRODUCT form is the fallback, guarded against blanks, or use a pivot table — which handles this natively and is far easier on a large dataset.
How do I count unique values matching a condition?
Filter first: COUNTA(UNIQUE(FILTER(A2:A100, B2:B100="East"))). Chaining the dynamic-array functions this way reads more clearly than any single-formula equivalent.
Why does the count change when I add rows below the range?
It does not, and that is the problem — a fixed range such as A2:A100 silently ignores anything added afterwards, so the figure quietly goes stale. Convert the data to a table, or reference a structured column, so the range grows with the data rather than needing to be edited.
Does trailing whitespace create false duplicates?
Yes, and it is the usual reason a count is higher than expected. "East" and "East " are distinct values to UNIQUE while looking identical on screen, so clean the column with TRIM on import rather than trying to spot them by eye.