How to Count Unique Values by Category in Excel?
Counting unique values within categories can enhance your data analysis. Use COUNTIFS or SUMPRODUCT to achieve this efficiently.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
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 a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.