COUNTUNIQUE counts distinct values across one or more ranges. It counts values rather than complete records, so use UNIQUE with ROWS when you need distinct multi-column rows.
COUNTUNIQUE function syntax
=COUNTUNIQUE(value1, [value2, ...])- value1: first value or range.
- Additional arguments: other values or ranges pooled into the same count.
Set up the example data
Enter this small dataset starting in A1. The first row contains headers. Keep the formula output separate from the input cells.
| Name | Region |
|---|---|
| Alice | East |
| Bob | West |
| Alice | East |
| Carol | East |
| Dan | West |
| Bob | East |
Count distinct names
Enter this formula in A10. Six entries contain four distinct names. Alice and Bob each count once.
In our tests, a blank-only range and an empty-string value each returned zero. A cell containing spaces is text, so clean unwanted spaces before comparing distinct values.
=COUNTUNIQUE(A2:A7)Result: 4.
Pool values across ranges
Both ranges feed one shared count. The function does not count each range independently and add the answers.
=COUNTUNIQUE(A2:A4,A5:A7)Result: 4.
Check letter case
Different letter cases are distinct in this example. Normalize with LOWER when capitalization should not identify a separate value.
=COUNTUNIQUE({"Open";"open";"OPEN"})Result: 3.
Ignore capitalization
LOWER changes all three variants to the same string before COUNTUNIQUE counts them.
=COUNTUNIQUE(ARRAYFORMULA(LOWER({"Open";"open";"OPEN"})))Result: 1.
Count unique customers in one region
FILTER retains the East orders. Alice repeats, leaving three distinct customers: Alice, Carol and Bob.
=COUNTUNIQUE(FILTER(A2:A7,B2:B7="East"))Result: 3.
Return zero for an absent group
Check for matching rows before counting FILTER’s result. Do not assume a FILTER error is automatically a count of zero.
=IF(COUNTIF(B2:B7,"South")=0,0,COUNTUNIQUE(FILTER(A2:A7,B2:B7="South")))Result: 0.
Count distinct records
UNIQUE removes repeated complete rows. ROWS counts the remaining records, so Bob in East and Bob in West remain different records.
=ROWS(UNIQUE(A2:B7))Result: 5.
Other Google Sheets articles you may also like