FormulaMath
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
=COUNTA(UNIQUE(A2:A200))
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.