How to Count Unique Values by Category in Excel?
Count Unique Values by Category is a Excel function that counts the number of unique values in a specified category.. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Counting unique values within categories can enhance your data analysis. Use COUNTIFS or SUMPRODUCT to achieve this efficiently.
The Formula
"Count unique values within each category (e.g. unique customers per region) using COUNTIFS or SUMPRODUCT"
=SUMPRODUCT(1/COUNTIFS(A:A, A:A, B:B, B:B))This formula counts the number of unique values in a specified category.
Step-by-Step Breakdown
- COUNTIFS counts occurrences of each value in the specified range based on the category criteria.
- 1/COUNTIFS converts the counts into fractions, where each unique value contributes 1.
- SUMPRODUCT sums these fractions to give the total count of unique values.
- Ensure that the ranges in COUNTIFS match the data you are analyzing.
Edge Cases & Warnings
- Empty cells in the category or value range may lead to incorrect counts.
- Duplicate values in the category may cause miscounts if not handled properly.
- Using non-contiguous ranges can result in errors or inaccurate results.
- Data types mismatch (e.g., numbers stored as text) can affect the count.
Examples
"Categories: A, A, B, B, C; Values: 1, 1, 2, 3, 3"
3 unique values (1, 2, 3) for categories A, B, C"Categories: North, North, South; Values: 5, 5, 6"
2 unique values (5, 6) for categories North and SouthFrequently Asked Questions
Can I use this formula for non-contiguous ranges?
No, COUNTIFS requires contiguous ranges for accurate results.
What if my data contains blank cells?
Blank cells may be counted as unique; consider filtering them out.
Is there a simpler formula for small datasets?
For small datasets, using a pivot table may be easier for counting unique values.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.