Formula example
Count Unique Values in Google Sheets Formula
Count how many distinct owners appear in a task list while ignoring blank cells. With the sample data, the formula returns 3: Maya, Nico, and Iris.
Copyable formula
=IFERROR(COUNTUNIQUE(FILTER(B2:B100, B2:B100<>"")), 0)Returns 3 unique nonblank owners from the sample data.
Useful variations
=IFERROR(COUNTUNIQUE(FILTER(B2:B100, B2:B100<>"")), 0)Use this for a simple one-column distinct count in Google Sheets.
=IFERROR(COUNTUNIQUE(ARRAYFORMULA(TRIM(FILTER(B2:B100, B2:B100<>"")))), 0)Use this when values that look the same may contain leading or trailing spaces.
=UNIQUE(FILTER(B2:B100, B2:B100<>""))Use this when you want to inspect the distinct values instead of only counting them.
=COUNTA(UNIQUE(FILTER(B2:B100, B2:B100<>"")))Excel uses UNIQUE plus COUNTA instead of COUNTUNIQUE.
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 |
When to use this formula
- You need a single number for distinct names, domains, SKUs, owners, or categories.
- Blank cells should not increase the count.
- The source range may still be empty and should return 0 instead of a formula error.
Why FILTER is included
COUNTUNIQUE can count a blank-looking value when the range includes formulas or inconsistent blanks. FILTER(B2:B100, B2:B100<>"") removes empty cells before the distinct count runs.
Clean hidden spaces before counting
Maya and Maya with a trailing space look identical in the grid but count as different values. Use the TRIM version when the data comes from exports, forms, or pasted lists.
Google Sheets and Excel difference
Google Sheets has COUNTUNIQUE. Excel does not use that function name, so modern Excel workbooks usually combine UNIQUE, FILTER, and COUNTA.
Returned count from the sample data
| Range | Unique nonblank values | Returned count |
|---|---|---|
| B2:B5 | Maya, Nico, Iris | 3 |
| B2:B100 with blank rows below | Maya, Nico, Iris | 3 |
| Empty owner range | None | 0 |
Formula choices
| Need | Formula pattern | Use when |
|---|---|---|
| Count unique values | COUNTUNIQUE(FILTER(range, range<>"")) | You need a single number. |
| Clean spaces first | COUNTUNIQUE(TRIM(FILTER(...))) | Imported values may contain hidden spaces. |
| Show the unique values | UNIQUE(FILTER(range, range<>"")) | You want to inspect the distinct list. |
| Use Excel | COUNTA(UNIQUE(FILTER(...))) | The workbook is in Excel 365. |
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 formula 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 and UNIQUE pattern so each row has one comparable key.
Why is my unique count too high?
Hidden spaces, mixed case, or nonprinting characters usually make values look the same while counting as different values.
How do I count unique domains after extracting them?
Extract domains into a helper column, then run the nonblank COUNTUNIQUE formula on that helper column.