"How many different customers are in this list?" is a counting-unique question, not a plain count. COUNT and COUNTA tell you how many entries there are, including repeats. To count how many distinct values appear, use one of the two methods below depending on your Excel version.
Modern Method: COUNTA + UNIQUE
In Microsoft 365 and Excel 2021, UNIQUE returns the list of distinct values, and COUNTA counts them:
=COUNTA(UNIQUE(A2:A100))
UNIQUE spills the unique entries; COUNTA counts that spilled list. This is the clearest method and updates automatically when the data changes.
To count unique values that are not blank, filter the blanks out first:
=COUNTA(UNIQUE(FILTER(A2:A100, A2:A100<>"")))
Count Unique That Appear Only Once
UNIQUE has a third argument. Set it to TRUE to return only the values that appear exactly once, then count them:
=COUNTA(UNIQUE(A2:A100, , TRUE))
Classic Method: SUMPRODUCT + COUNTIF
Excel 2019 and earlier have no UNIQUE function. This formula counts distinct non-blank values in any version:
=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100))
It works by giving each value a weight of 1 divided by how many times it appears. A value that appears 4 times contributes 1/4 four times, which sums to 1. Every distinct value therefore adds exactly 1, and the total is the count of distinct values.
Note: this version errors on blank cells (division by zero). If the range has blanks, use:
=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100, A2:A100&""))
Count Unique Text or Numbers Only
Combine COUNTIF with a type check. Unique numbers only:
=SUMPRODUCT((COUNTIF(A2:A100, A2:A100)=1)*ISNUMBER(A2:A100))
Count Unique With a Condition
To count distinct values that meet a criterion (for example, distinct customers in the "East" region), the modern approach nests FILTER inside UNIQUE:
=COUNTA(UNIQUE(FILTER(A2:A100, B2:B100="East")))
Common Problems
- #DIV/0! in the classic formula. The range contains a blank cell. Use the &"" version above, which treats blanks safely.
- #NAME? on UNIQUE. Your Excel version is 2019 or older. Use the SUMPRODUCT method instead.
- Count looks too high. Trailing spaces make "East" and "East " count as two different values. Wrap the range in TRIM, or clean the data first.
- Case sensitivity. COUNTIF and UNIQUE treat "apple" and "Apple" as the same value. For a case-sensitive unique count, you need an EXACT-based array formula.
Frequently Asked Questions
What is the formula to count unique values in Excel?
In Excel 365 or 2021, use =COUNTA(UNIQUE(A2:A100)). In Excel 2019 or earlier, use =SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100)) for a range with no blanks.
What is the difference between COUNT and count unique?
COUNT and COUNTA count every entry, including duplicates. Counting unique values counts each distinct value only once, so a column with 100 rows and 12 different customers returns 12, not 100.
How do I count unique values that meet a condition?
Nest FILTER inside UNIQUE: =COUNTA(UNIQUE(FILTER(A2:A100, B2:B100="East"))) counts the distinct values in column A where column B is "East".
Why does SUMPRODUCT/COUNTIF return a #DIV/0! error?
The range contains at least one blank cell, and dividing by a zero count causes the error. Use =SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100, A2:A100&"")), which handles blanks safely.