Formula example
Count Unique Values in Google Sheets
You want to count how many distinct owners appear in a task list, ignoring blank cells.
Copyable formula
=IFERROR(COUNTUNIQUE(FILTER(B2:B100, B2:B100<>"")), 0)Sample data
| Task | Owner | Status | Due Date |
|---|---|---|---|
| Import leads | Maya | Complete | 2026-01-06 |
| Clean headers | Nico | In Progress | 2026-01-08 |
| Review budget | Maya | Complete | 2026-01-12 |
| Publish report | Iris | Blocked | 2026-01-15 |
Formula explanation
- FILTER removes blank owner cells before counting.
- COUNTUNIQUE counts distinct remaining values.
- IFERROR returns 0 if the range has no nonblank values yet.
Common errors
- COUNTUNIQUE alone may count blanks depending on the range contents.
- FILTER returns no matches when the range is empty unless you wrap the formula in IFERROR.
- Hidden spaces can create values that look identical but count separately.
- Use TRIM on source data if names are inconsistent.
Build your own version
Use the deterministic builder for this pattern: Count Unique Formula Builder.
Related formulas
FAQ
Does Excel have COUNTUNIQUE?
Excel does not use COUNTUNIQUE. Use UNIQUE with COUNTA in modern Excel.
Can this count unique rows?
For full rows, combine columns first or use a QUERY/UNIQUE pattern.