Count Distinct Values in Excel Without Counting Blanks as One
A customer list containing six filled rows can contain only four different customer codes. A second question asks how many codes appear exactly once, which gives a different answer. State which count you need before selecting a formula, and include an empty-range test so a blank list does not unexpectedly count as one.
Define distinct and exactly once with the same source
Use Microsoft 365, Excel 2024, or Excel 2021 for the formulas in this guide. Paste the download into A1:A9, retaining the two empty records at worksheet rows 5 and 8. The column contains text codes with a consistent prefix. This intentionally avoids mixing a numeric value such as 1 with a text identifier such as 001.
C-01 appears twice and C-02 appears twice. C-03 and C-04 each appear once. A distinct-customer count should include every different customer once, yielding four. An exactly-once count identifies customers with only one occurrence, yielding two. Neither interpretation is inherently better; the correct one depends on whether the report measures customer coverage or one-time appearances.
| Metric | Expected value |
|---|---|
| Nonblank source entries | 6 |
| Distinct nonblank codes | 4 |
| Codes occurring exactly once | 2 |
Show the distinct list before trusting its count
Put Distinct code in C1 and enter the following formula once in C2. In the normal example, C2:C5 should show C-01, C-02, C-03, and C-04. Keeping the list visible makes the summary reviewable: another person can compare the four labels to the source instead of accepting an unexplained scalar.
The filter condition excludes both genuinely empty cells and formula results equal to empty text. It does not remove a cell containing one or more spaces. In this exercise every filled code has been deliberately entered without leading or trailing spaces; if your real export does not meet that rule, inspect and normalize a helper column before counting.
=UNIQUE(FILTER(A2:A9,A2:A9<>"",""))
Count nonempty results and test an empty source
Enter the answer formula in F2. It compares the deduplicated result to empty text, turns each nonempty result into 1, and adds those contributions. The final comparison matters because the no-data fallback is itself a result. For this sample the four actual codes contribute four ones; for an empty source the fallback contributes zero.
Test that second path on a copy by clearing A2:A9 while retaining the header. F2 should become 0. Avoid testing by merely hiding rows, because row visibility is not the criterion used here. Restore the supplied data afterward. This pair of tests catches the common mistake of treating an empty fallback message as an actual customer.
=SUM(--(UNIQUE(FILTER(A2:A9,A2:A9<>"",""))<>""))Count the codes that occur exactly once
Use a separate result area, such as H2, for the exactly-once list. The TRUE argument asks for codes without repeated occurrences. In this sample H2:H3 should contain C-03 and C-04, and C-01 must not appear at all. This is a useful check when someone casually calls both operations unique counting.
The companion count below turns an empty exactly-once result into zero. Its IFERROR also catches other formula errors, so do not use that zero as evidence that a damaged source is clean. Review source errors separately before distributing a report. During troubleshooting, use the list formula without the wrapper so unexpected errors remain visible.
H2: =UNIQUE(FILTER(A2:A9,A2:A9<>"",""),,TRUE)
K2: =IFERROR(SUM(--(UNIQUE(FILTER(A2:A9,A2:A9<>"",""),,TRUE)<>"")),0)
Set rules for spaces, case, and identifier types
Do not silently clean customer codes just to obtain a smaller count. First decide whether surrounding spaces are accidental and whether letter case distinguishes two real identifiers. The examples here use uppercase text codes, so they do not test a case-sensitive identity policy. If case is meaningful in your system, this basic formula is not a substitute for an explicitly case-sensitive method.
Leading zeros deserve the same attention. Import identifiers as text before running the comparison, and preserve the original column alongside any cleaned helper. Once 001 has been converted into numeric 1, a distinct-count formula cannot reconstruct which original spelling was intended. For numeric measures, choose numeric comparison rules instead of copying these identifier assumptions.
Adapt the bounded formula and export a reviewable result
All references in the exercise end at A9. When the source grows, extend both occurrences of that range together or move the source to a Table with an appropriate structured column reference. Leave the output area clear and keep the count outside the returned list. A blocked output list and a correct scalar count can coexist, which is another reason to review both.
The downloads include distinct and exactly-once expected lists as well as the original entries. Their checks compare nonempty strings and occurrence counts independently in Python; no native Excel recalculation is claimed. Before publication or use in a business report, test the normal input, all-empty input, all-repeated input, and one-entry input in the target Excel version.
Common questions
Why can COUNTA with UNIQUE return 1 for an empty list?
An empty-text fallback or a returned placeholder can still be a countable result. This guide counts results that are not empty text and explicitly tests an all-empty source.
Does distinct mean each value occurs only once in the source?
No. Distinct means each different value is represented once in the answer. Exactly once excludes any value that repeats in the source; the sample counts are 4 and 2 respectively.
Does the formula ignore cells containing spaces?
No. A space is text, not an empty string. Decide whether spaces are accidental, inspect the original values, and count a reviewed helper column if cleanup is appropriate.
Sources & method
- Microsoft: UNIQUE function ↗
- Microsoft: FILTER function ↗
- Microsoft: Use COUNTA to count cells that are not blank ↗
- Microsoft: Ways to count values in a worksheet ↗
Examples and diagrams use synthetic data. Application instructions follow the linked documentation; available menus and options can vary. Our sample checks do not establish behavior in every Excel or Google Sheets version. Read our AI-assisted editorial method.