How to Count Unique Values in Google Sheets
=COUNTUNIQUE(range) to count the distinct values in a range. To count only the values that appear exactly once, use =COUNTA(UNIQUE(range, FALSE, TRUE)).
Count distinct values
A list of orders may have 100 rows but only 20 different customers. COUNTUNIQUE answers the second question: how many different values are there, counting each value once however often it appears.
COUNTUNIQUE syntax
=COUNTUNIQUE(value1, [value2, ...])
value1: the first value or range to check.value2, ...(optional): more values or ranges.
Per Google's help page, counting unique rows across multiple columns is not supported. The page also does not say how blank cells, text and numbers are treated, so test those cases on your own data.
A step-by-step example
Here is a list of names in cells A1:A7, with repeats.
| A | |
|---|---|
| 1 | Name |
| 2 | Ann |
| 3 | Ben |
| 4 | Ann |
| 5 | Cara |
| 6 | Ben |
| 7 | Ann |
To count the different names:
=COUNTUNIQUE(A2:A7)
This returns 3: Ann, Ben and Cara. Ann appears three times and Ben twice, but each counts once.
Count items that appear only once
COUNTUNIQUE counts every distinct value, including those that repeat. To count only the values that appear a single time, combine UNIQUE with COUNTA. The third argument of UNIQUE, exactly_once, keeps only entries without duplicates:
=COUNTA(UNIQUE(A2:A7, FALSE, TRUE))
This returns 1, because only Cara appears exactly once. Here is why:
UNIQUE(A2:A7, FALSE, TRUE)returns a list of the entries that appear once: just Cara.- COUNTA counts the values in that list: 1.
Count how often one value appears
For a single value, COUNTIF is simpler: =COUNTIF(A2:A7, "Ann") returns 3. See the COUNTIF guide.
Common mistakes
- Hidden spaces.
"Ann"and"Ann "count as two different values. Clean the data with TRIM. - Mixed formats. A number and the same number as text are different.
- Expecting unique rows across columns. COUNTUNIQUE does not support it. Use UNIQUE on the columns and count the result with COUNTA.
Try it yourself
Use the name list above (A1:A7). Type your formula into the box, starting with =, then press Check.
Exercise 1: Distinct names
Write a formula that counts how many different names are in A2:A7.
Exercise 2: Names that appear once
Write a formula that counts how many names in A2:A7 appear exactly once. Combine COUNTA and UNIQUE.
What to learn next
- UNIQUE: remove duplicates from a list.
- COUNTIF: count the rows that match a condition.
- COUNT and COUNTA: count numbers or non-empty cells.
Syntax and rules in this guide were checked against Google's Count unique values help page. See all guides.