How to Count Unique Values in Google Sheets
Type =COUNTA(UNIQUE(A2:A)) to count how many different values a column holds — duplicates are collapsed first, then counted.
=COUNTA(UNIQUE(A2:A))
UNIQUE removes duplicates, COUNTA counts what is left. Trim the range if trailing blanks inflate the count.
How it works
There is no single COUNTUNIQUE-style function that handles every case, so the reliable pattern is to deduplicate with UNIQUE and then count the result with COUNTA. Watch trailing blank cells: an open range like A2:A includes empty rows, and UNIQUE keeps one blank, adding 1 to your count. Wrap the range in FILTER to drop empties for an exact figure.
Variations
Count unique, ignoring blanks
=COUNTA(UNIQUE(FILTER(A2:A,A2:A<>"")))
FILTER strips empty cells before counting.
Count unique values that meet a condition
=COUNTA(UNIQUE(FILTER(A2:A,B2:B="West")))
Only counts distinct A values where B is West.
Legacy shortcut
=COUNTUNIQUE(A2:A)
COUNTUNIQUE also works but does not accept extra conditions.
Examples
| Scenario | Formula |
|---|---|
| Number of distinct customers | =COUNTA(UNIQUE(B2:B)) |
| Distinct products sold in the West | =COUNTA(UNIQUE(FILTER(A2:A,C2:C="West"))) |
FAQ
Is there a COUNTUNIQUE function?
Yes, =COUNTUNIQUE(A2:A) exists and is simplest for a plain count. Use COUNTA(UNIQUE()) when you need to filter or combine ranges first.
Why is my unique count one too high?
An open range like A2:A includes blank rows, and UNIQUE keeps one empty value. Filter blanks: =COUNTA(UNIQUE(FILTER(A2:A,A2:A<>""))).
How do I count unique values with two conditions?
Nest FILTER conditions: =COUNTA(UNIQUE(FILTER(A2:A,(B2:B="West")*(C2:C="2024")))). Multiply conditions for AND logic.