Excel spreadsheets often contain lists where you need to know how many unique values appear. Learning how to count number of unique values in excel helps you clean data, validate lists, and summarize survey responses accurately.
This guide walks through practical methods, from simple formulas to dynamic tools, so you can apply the right technique for your dataset size and structure.
| Method | When to Use | Complexity | Dynamic Update |
|---|---|---|---|
| Remove Duplicates + COUNTA | Quick one-time check on a small list | Low | No |
| SUMPRODUCT with COUNTIF | No dynamic array, works in most Excel versions | Medium | Yes |
| UNIQUE + COUNTA (Excel 365) | Dynamic spills and live ranges | Low | Yes |
| Power Query distinct count | Large tables, repeated reports, automation | Medium | Yes via refresh |
Using Remove Duplicates and COUNTA for a Quick Count
For a simple, manual approach, copy your data range to a new location and use Remove Duplicates. Select the copied range, open Data > Remove Duplicates, and choose the relevant column. Excel reduces the list to unique items, so you can then apply COUNTA to the result and obtain how many unique values in excel quickly.
This technique is fast for one-off checks, but it changes the data order and overwrites existing content if you work in-place. Use it when you need a straightforward snapshot rather than a formula that updates automatically each time the source changes.
Keep the original list untouched on another sheet so you can rerun the process later without losing the raw data. Because Remove Duplicates modifies the visible set, combine it with COUNTA only on the cleaned output to get an accurate distinct count at that moment.
SUMPRODUCT with COUNTIF for Compatibility
If your workbook must run on older Excel versions without dynamic array functions, SUMPRODUCT combined with COUNTIF is a reliable pattern. The formula =SUMPRODUCT(1/COUNTIF(range,range)) counts how many unique values in excel by weighing each occurrence inversely and summing the results.
Wrap the core with IF to ignore blanks, such as =SUMPRODUCT((range"")/COUNTIF(range,range)), to avoid division errors and ensure empty cells do not skew the distinct count. This method updates dynamically when source values change, but it may slow down very large ranges because COUNTIF processes the entire array twice.
Use this approach when you need a single, compact formula that works across Excel 2016 and later, and when your dataset fits comfortably within memory limits. Test with a subset first to confirm that error values or blanks do not produce unexpected results.
UNIQUE and COUNTA in Excel 365
Excel 365 users can count distinct entries with minimal effort by nesting the UNIQUE function inside COUNTA. For example, =COUNTA(UNIQUE(range)) spills a list of unique values and then counts them, giving an immediate how many unique values in excel total that updates automatically.
To handle blanks gracefully, extend the formula with =COUNTA(UNIQUE(FILTER(range,range""))), which removes empty cells before the distinct operation. This dynamic duo works seamlessly with structured tables, so changing source data instantly reflects in the count without manual steps.
Because UNIQUE is a dynamic array function, you can also reuse its output elsewhere, such as in charts or downstream calculations, making it the most flexible choice for modern Excel workflows.
Power Query for Scalable and Reusable Distinct Counts
When working with large tables or recurring reports, Power Query provides a robust way to count distinct entries. Load your data, select the target column, choose Distinct Rows, and then add a Custom Column that counts the resulting rows to capture how many unique values in excel exist within each group or overall.
This method centralizes data preparation, keeps complex transformations separate from your worksheet, and refreshes with a single click. It also supports parameterized queries, so you can reuse the same flow across multiple sheets or workbooks while maintaining consistent logic.
Schedule refreshes via Excel data model or Power Automate if you need periodic updates, which is especially valuable for dashboards that rely on accurate distinct counts derived from ever-growing operational data.
Choosing the Right Technique for Your Workflow
- For one-time checks on small lists, use Remove Duplicates plus COUNTA for simplicity.
- For dynamic, compact formulas that work across versions, apply SUMPRODUCT with COUNTIF while handling blanks.
- On Excel 365, leverage UNIQUE + COUNTA for live, spill-ready distinct counts.
- For large datasets or automated reports, adopt Power Query to standardize distinct counting and refresh on demand.
- Always test edge cases like blanks, mixed case text, and error values before deploying a method in production.
FAQ
Reader questions
Will removing duplicates delete my original data permanently?
Yes, Remove Duplicates modifies the selected range in place, so always copy the data to a new location first or keep a backup copy of the original sheet.
How do I count unique text values while ignoring case sensitivity?
Normalize the text with LOWER or UPPER inside UNIQUE, such as =COUNTA(UNIQUE(LOWER(range))) , so that 'ABC' and 'abc' are treated as the same item.
Can I count unique values based on multiple conditions across columns?
Combine criteria into a single helper column using concatenation or the TEXTJOIN function, then apply UNIQUE + COUNTA on that helper to count distinct combinations.
Why does my SUMPRODUCT formula return an error when there are blanks?
Blank cells cause COUNTIF to return zero, creating a division-by-zero error; fix it by excluding blanks with =SUMPRODUCT((range<>"")/COUNTIF(range,range)) or by wrapping in IF.