To count unique values in Excel while ignoring blanks, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A10,A2:A10<>""))),0). This counts each different value once, excludes empty cells and formulas returning "", and returns 0 when the range contains no values.
This formula requires Excel for Microsoft 365, Excel 2024, or Excel 2021, including their Mac versions. Excel 2019 and 2016 need the older formula below. Microsoft's UNIQUE documentation lists the supported versions.
You can download the count unique values example workbook to follow the examples and practice changing the data.
Distinct Values vs. Values That Appear Exactly Once
People use “unique” to mean two different things:
| What you want to count | Meaning | Example: Apple, Banana, Apple |
|---|---|---|
| Distinct values | Count each different value once, regardless of repetition | 2: Apple and Banana |
| Values appearing exactly once | Count only values with no duplicates | 1: Banana |
The default UNIQUE function returns distinct values. Its optional exactly_once argument returns only values that occur once. Decide which result you need before choosing the formula.
Count Unique Values and Ignore Blanks
On a worksheet named Example, enter Value in A1 and set up this list:
| Cell | What to enter | What it displays |
|---|---|---|
| A2 | Apple | Apple |
| A3 | Banana | Banana |
| A4 | Apple | Apple |
| A5 | Leave the cell empty | Nothing |
| A6 | Cherry | Cherry |
| A7 | ="" | Nothing |
| A8 | Banana | Banana |
| A9 | Date | Date |
| A10 | Leave the cell empty | Nothing |
Do not type the words “Leave the cell empty” into the worksheet. In A7, enter the formula ="", including the equals sign.
Enter this formula in D2 and press Enter:
=IFERROR(ROWS(UNIQUE(FILTER(A2:A10,A2:A10<>""))),0)
The result is 4: Apple, Banana, Cherry, and Date.
Read the formula from the inside out:
A2:A10<>""identifies cells whose values are not empty.FILTERkeeps those values, excluding both the empty cells and the empty string inA7.UNIQUEreduces the remaining list to one of each value.ROWScounts the rows in that single-column result.IFERRORreturns 0 if the calculation produces an error, including the empty-array error when nothing qualifies.
Keep the range to one column for this pattern. If you supply several columns, UNIQUE compares whole rows, which answers a different question.
Keep numeric zero in the count
Zero is a value. If you enter the number 0 in A10, the distinct count rises from 4 to 5. The condition <>"" keeps numeric zero while excluding empty values.
Clear A10 again before continuing with the original example. Avoid adding a <>0 condition unless your task specifically calls for excluding zero.
Count Values That Appear Only Once
Enter this formula in D3:
=IFERROR(ROWS(UNIQUE(FILTER(A2:A10,A2:A10<>""),,TRUE)),0)
The result is 2: Cherry and Date. Apple and Banana each appear twice, so neither counts.
The two commas before TRUE leave the second argument, by_col, at its default. TRUE sets the third argument, exactly_once. If every nonblank value has a duplicate, the formula returns 0.
This distinction matters for customer lists. A distinct count tells you how many different customers ordered; an exactly-once count tells you how many customers have just one entry in the selected data.
Return Zero for an Empty Range
For a separate test, leave A15 and A17 empty and enter ="" in A16. In D7, enter:
=IFERROR(ROWS(UNIQUE(FILTER(A15:A17,A15:A17<>""))),0)
The result is 0.
The formula deliberately omits FILTER's optional if_empty argument. When nothing qualifies, FILTER produces an empty-array error, which the outer IFERROR converts to zero. Microsoft's FILTER reference documents this #CALC! behavior.
Do not insert "" as FILTER's third argument in this counting pattern. That supplies a single empty-string result, and ROWS can count it as one row.
IFERROR also hides other calculation errors. If your count unexpectedly shows zero, temporarily remove the outer wrapper and inspect the source data. An error in the input or an incorrect reference needs fixing; it should not be interpreted as an empty list. See the IFERROR function reference for its error-handling behavior.
When the Shorter COUNTA Formula Works
If every cell in your range contains a valid, nonempty value, you can use:
=COUNTA(UNIQUE(A2:A4))
For Apple, Banana, Apple, this returns 2. Enter it in D5 to check.
The COUNTA function counts populated results, including empty strings and errors. A truly blank source cell can also appear as a zero in a UNIQUE result. Consequently, COUNTA(UNIQUE(A2:A10)) is not a reliable way to exclude blanks from the full example.
Use the longer FILTER-and-ROWS formula when your source may contain empty cells or formulas returning "".
Count Unique Values with a Condition
Suppose you want the number of different products sold in the West region. On the same worksheet, enter Region in F1, Product in G1, and these records in F2:G7:
| Row | Region (F) | Product (G) |
|---|---|---|
| 2 | West | Apple |
| 3 | West | Banana |
| 4 | East | Cherry |
| 5 | West | Apple |
| 6 | West | Leave empty |
| 7 | West | Date |
Leave G6 genuinely empty. Enter West in I2, then put this formula in J2:
=IFERROR(ROWS(UNIQUE(FILTER($G$2:$G$7,($F$2:$F$7=I2)*($G$2:$G$7<>"")))),0)
The result is 3: Apple, Banana, and Date. The multiplication sign joins two conditions: the region must match I2, and the product must be nonblank. Both ranges must cover the same rows.
Enter North in I3 and copy J2 down to J3. The fixed source ranges stay in place, while the criterion changes to I3. Because there are no North records, the result is 0.
Filtering happens before counting distinct values, so a product's appearances in other regions do not affect this result. If you need to check which items occur in a second list, see how to compare two columns in Excel.
Count Unique Values in Excel 2019 or 2016
For a simple text list like the fruit example, enter this formula in D4:
=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&""))
Press Enter; this SUMPRODUCT formula does not require Ctrl+Shift+Enter. With the original data and A10 empty, it returns 4.
COUNTIF supplies each value's frequency. Each Apple contributes 1/2, and the two Apple rows total 1. Banana works the same way; Cherry and Date contribute 1 each. The numerator makes blank and empty-string entries contribute zero. The &"" supplies text criteria, including an empty-string criterion for the blank entries.
SUMPRODUCT adds those contributions. See Microsoft's SUMPRODUCT documentation for the function behavior. Use a bounded range such as A2:A1000 instead of a whole-column reference for this calculation.
Treat this as a fallback for ordinary, clean labels, with these limits:
COUNTIFinterprets*and?as wildcards, and~as an escape character. Literal characters such asA*in product codes can therefore produce incorrect counts.- Comparison-like text, long text, errors, and mixed numbers and numeric text can make COUNTIF-based matching unsuitable for the data.
- This formula counts distinct values; it does not calculate the exactly-once count shown earlier.
For example, do not use this fallback unchanged to count a list containing the literal labels A* and Apple. Inspect and test such data before choosing a legacy counting method. Microsoft's COUNTIF guide explains its wildcard rules and matching limits.
Why Your Unique Count Looks Wrong
Letter case does not make a separate value
The formulas here are not case-sensitive: Apple and APPLE count as the same label. If uppercase and lowercase distinguish your IDs, you need a case-sensitive method instead.
Spaces and data types can change the result
Apple and Apple contain different text because the second value has a trailing space. A cell containing only spaces also passes the <>"" test. Check imported data for spaces and invisible characters before treating the count as final.
Decide whether numbers stored as text should be the same as actual numbers. For identifiers, leading zeros may be meaningful; for quantities, consistent numeric storage usually makes more sense. Cell formatting alone does not clean or convert the underlying values.
A standalone unique list needs space to spill
To inspect the actual distinct values, enter this in a clear area of the sheet:
=UNIQUE(FILTER(A2:A10,A2:A10<>""))
With the original example, it spills Apple, Banana, Cherry, and Date into four rows. If it shows #SPILL!, clear the cells blocking that list or move the formula.
The counting formulas wrapped in ROWS return a single number. They do not need empty cells beneath them for the intermediate list.