Cleanup formula page
Count Unique Values Formula
Use this when a report needs the number of distinct IDs, owners, emails, or categories.
Count unique values in Excel or Google Sheets.
The formula returns the count of distinct nonblank values.
Copy formulas
=IFERROR(COUNTA(UNIQUE(FILTER(A2:A100, A2:A100<>""))), 0)=IFERROR(COUNTUNIQUE(FILTER(A2:A100, A2:A100<>"")), 0)Excel uses UNIQUE plus COUNTA for the filtered spill result, while Google Sheets provides COUNTUNIQUE directly. Both shown formulas exclude blank cells before counting.
Example data
| Region | Status | |
|---|---|---|
| maya@example.com | East | Active |
| noah@example.com | West | Active |
| maya@example.com | East | Active |
| West | Inactive |
The formula returns the count of distinct nonblank values.
How the formula works
- Blank cells are filtered out first.
- Unique values are counted after filtering.
- IFERROR returns 0 when the source range has no nonblank values.
| Syntax piece | Role in the formula |
|---|---|
| A2:A100<>"" | Filters out empty source cells before distinct values are counted. |
| UNIQUE plus COUNTA | The Excel combination returns and counts the distinct filtered values. |
| COUNTUNIQUE | The Google Sheets function counts distinct values without a separate UNIQUE spill. |
| IFERROR | Returns 0 when the filtered input has no usable values. |
Verified examples
=IFERROR(COUNTA(UNIQUE(FILTER(A2:A100, A2:A100<>""))), 0)Excel: Put Maya@example.com, Lee@example.com, Maya@example.com, and one blank in A2:A100. Returns: 2
=IFERROR(COUNTUNIQUE(FILTER(A2:A100, A2:A100<>"")), 0)Google Sheets: Use the same two-email sample, duplicate Maya@example.com, and one blank in A2:A100. Returns: 2
=IFERROR(COUNTUNIQUE(FILTER(A2:A100, A2:A100<>"")), 0)Google Sheets: Leave A2:A100 empty. Returns: 0
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| Values that look identical count twice | One value contains leading, trailing, or nonbreaking spaces. | Normalize the source text before counting unique values, for example with a cleaned helper column. |
| Blank-looking formula results are counted | Cells contain formulas returning an empty string, which can behave differently from truly empty cells in a source range. | Filter on the displayed value or use a helper column that converts empty-string results to genuine blanks. |
| Excel reports that UNIQUE is unavailable | The workbook uses an Excel version without dynamic-array functions. | Use a compatible helper-column or pivot-table method, or run the formula in a current Excel version. |
When not to use this formula
- Do not use these formulas when distinct values must be grouped by several conditions; filter the source by those conditions first.
Alternatives
| Alternative | When to use it |
|---|---|
| Unique List Formula | Use when the distinct values themselves should spill into a visible list. |
| Count Unique Formula Builder | Use when a Google Sheets count needs a configurable range and output formula. |
Related formulas
Official references
- UNIQUE function from Microsoft
- COUNTUNIQUE function from Google
FAQ
Are blank cells included?
No. FILTER removes cells equal to an empty string before UNIQUE plus COUNTA or COUNTUNIQUE counts the values.
Why are there two formulas?
COUNTUNIQUE is native to Google Sheets; current Excel uses UNIQUE to produce distinct values and COUNTA to count them.