How to Count Unique Values in Excel?
Count Unique Values in Excel is a Excel function that counts the number of unique values in a specified column while ignoring duplicates and blank cells.. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Counting unique values in a column can be tricky, but this formula will help you exclude duplicates and blanks effortlessly.
The Formula
"Count the number of distinct unique values in a column, excluding blanks and duplicates"
=SUMPRODUCT(1/COUNTIF(A:A, A:A&""))This formula counts the number of unique values in a specified column while ignoring duplicates and blank cells.
Step-by-Step Breakdown
- COUNTIF(A:A, A:A&"") counts occurrences of each value in the column.
- 1/COUNTIF returns an array of fractions representing the unique counts.
- SUMPRODUCT sums the array of fractions to give the total number of unique values.
- The &"" part ensures that blanks are excluded from the count.
Edge Cases & Warnings
- The column contains only blank cells.
- All values in the column are duplicates.
- The column has mixed data types (text and numbers).
- The formula is applied to a very large dataset, which may affect performance.
Examples
"A1:A5 with values {1, 2, 2, 3, ""}"
3"B1:B6 with values {"apple", "banana", "apple", "", "orange", "banana"}"
3Frequently Asked Questions
Can this formula count unique values across multiple columns?
No, this formula only counts unique values in a single column.
What if my data has errors like #N/A?
Errors will be counted as unique values unless handled separately.
How can I count unique values in a filtered list?
You may need to use a different approach, such as using SUBTOTAL with COUNTIFS.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.