Type =COUNTUNIQUE(A1:A100) into an empty cell, swapping A1:A100 for your own range, and you'll get the number of different values in that list. It's the quickest way to count unique values in Google Sheets. Just don't expect it to count unique rows across several columns, because that isn't supported.
What COUNTUNIQUE is and what it is not
COUNTUNIQUE counts how many unique values sit in a list of values and ranges you give it. The syntax is COUNTUNIQUE(value1, [value2, ...]), where value1 is the first value or range and anything after it is optional.
It isn't a row counter. Google's help page carries a tip saying that counting unique rows across multiple columns isn't supported, and that's the bit that trips people up.
Count distinct values in a range
- Click an empty cell where you want the answer.
- Type =COUNTUNIQUE( to start the formula.
- Enter your first range or value, for example A1:A100.
- Add any further ranges or values after a comma.
- Close the bracket and press Enter.
Worked COUNTUNIQUE examples
The first two formulas below are Google's own sample usage, and the notes show what they're doing.
| Formula | What it looks at |
|---|---|
| =COUNTUNIQUE(A1:C100) | Every value in three columns, treated as one pool of values |
| =COUNTUNIQUE(1,1,2,3,5,8,13,A2,B6:B9) | Typed numbers, one cell and one range, all mixed in the same formula |
In that second sample, the typed numbers alone give 6 unique values from 7 entries, because 1 appears twice. Whatever's in A2 and B6:B9 is then counted on top if it's new.
Here's a small example of why rows are different. Say A1:B3 holds London and Red, then London and Red again, then Paris and Blue. There are 2 unique rows, but =COUNTUNIQUE(A1:B3) looks at the individual values, so it finds London, Red, Paris and Blue and returns 4.
When COUNTUNIQUE is the wrong fix
- You care about whole records spanning columns, not single entries. This function can't judge that, so a multi-column result isn't a record count.
- You want another kind of count. Google lists COUNTIF, COUNTA, COUNTBLANK, COUNT, DCOUNT and DCOUNTA as the related functions, so check those on its help pages.
- You're adding numbers up against a condition, not counting. Our SUMIF guide is where you want to be.
- You're trying to find a value in a table. Go to the VLOOKUP walkthrough instead.
My advice: if your list sits in one column, use COUNTUNIQUE and move on. If you're really after rows, stop and rethink the question before you report that number to anyone.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
Frequently asked questions
How many ranges can one formula take?
It's specified with a maximum of 30 arguments, but Sheets supports an arbitrary number for this function.
Can I type values straight into the formula?
Yes. Google's sample mixes typed numbers with a cell and a range.
Is value2 required?
No. Only value1 is needed, and everything after it is optional.
Is it the same function as COUNTA?
No, they're separate. Google lists COUNTA as a related function, and COUNTUNIQUE's job is the number of unique values.
What should I enter as value1?
Either a single value or a range. In practice, that's normally the column you're checking.
