The quickest way to count unique values in Excel is to combine the UNIQUE function with COUNTA: =COUNTA(UNIQUE(range)). For example, if your list is in A2:A20, enter =COUNTA(UNIQUE(A2:A20)) to get the number of distinct entries. This works in Excel 365 and Excel 2021; older versions need a different approach (explained below).
How the UNIQUE + COUNTA Combination Works
The UNIQUE function, introduced in Excel 365 and Excel 2021, returns a list of distinct values from a range or array. It spills the results automatically into adjacent cells. When you wrap it with COUNTA, which counts non-empty cells, you get the total number of unique items.
Example: With a list of fruit names in A2:A10 (including duplicates like “Apple”, “Banana”, “Apple”), =COUNTA(UNIQUE(A2:A10)) returns 2 if only “Apple” and “Banana” appear. If the range includes blank cells, COUNTA ignores them, so blanks don’t inflate the count.
For a count of unique numeric values, you can also use =SUM(--(FREQUENCY(range, range)>0)) in older versions, but that’s more complex and requires an array formula (Ctrl+Shift+Enter in pre-dynamic-array Excel).
Using UNIQUE with COUNTA vs. SUMPRODUCT
Before Excel 365, the standard formula was =SUMPRODUCT(1/COUNTIF(range, range)). This counts unique values but has a serious flaw: if the range contains any blank cells, COUNTIF returns 0 for blanks, causing a #DIV/0! error. You’d need to adjust it to =SUMPRODUCT((range<>"")/COUNTIF(range, range&"")) to handle blanks.
The modern COUNTA(UNIQUE(range)) is simpler, faster, and handles blanks automatically. It also works with text, numbers, dates, and mixed data. For a unique count of values that meet a condition, you can combine UNIQUE with FILTER: =COUNTA(UNIQUE(FILTER(A2:A20, B2:B20="Yes"))).
Counting Unique Values with Criteria
Often you need to count unique items that satisfy a condition, such as unique customers who made a purchase in a specific region. Use FILTER inside UNIQUE:
Assume data in A2:A20 (customers) and B2:B20 (regions). To count unique customers from “West”:
- In a cell, enter
=COUNTA(UNIQUE(FILTER(A2:A20, B2:B20="West"))). - Press Enter. The formula spills the filtered list and counts unique entries.
This returns the number of distinct customer names in the “West” region. If you need a case-insensitive match, use EXACT or convert to lowercase with LOWER inside the condition.
Handling Blank Cells and Errors
Blank cells are automatically ignored by UNIQUE? Actually, UNIQUE will return a blank as a unique value if blanks exist in the range. For example, UNIQUE(A2:A10) with blanks returns a zero-length string, and COUNTA counts it as 1. To exclude blanks, use =COUNTA(UNIQUE(FILTER(A2:A10, A2:A10<>""))).
If your range contains errors (like #N/A), UNIQUE will return those errors, and COUNTA counts them. To ignore errors, wrap with IFERROR: =COUNTA(UNIQUE(IFERROR(range, ""))) but this requires an array formula in older versions.
Excel Versions and Compatibility
UNIQUE is only available in Excel 365 and Excel 2021. If you’re using Excel 2019 or earlier, you must use the SUMPRODUCT or FREQUENCY approach. For Excel 2019:
Use =SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20, A2:A20&"")) as an array formula (press Ctrl+Shift+Enter). This works but is less intuitive.
In Excel for the web and Mac, UNIQUE works the same as in Windows Excel 365. The keyboard shortcuts for array formulas differ: on Mac, press Cmd+Shift+Enter.
Common Problems and Misconceptions
One misconception is that UNIQUE counts unique values directly. It doesn’t; it returns the list. You must wrap it with COUNTA or ROWS. ROWS(UNIQUE(range)) also works but counts blanks if present.
Another issue: if your data is in a table, using structured references like Table1[Column1] works fine with UNIQUE. However, if you use a range that includes entire columns (e.g., A:A), UNIQUE will process 1,048,576 rows, which can slow down your workbook. Limit the range to your actual data.
Also, COUNTA counts cells that contain numbers, text, or logical values, but not truly empty cells. If a cell contains a formula that returns “” (empty text), COUNTA counts it. To avoid that, use COUNTA(UNIQUE(FILTER(range, range<>""))).
Alternatives for Older Excel Versions
If you cannot use UNIQUE, here are two reliable alternatives:
- SUMPRODUCT with COUNTIF:
=SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20, A2:A20&""))– works in all versions, but must be entered as an array formula in Excel 2019 and earlier (Ctrl+Shift+Enter). - FREQUENCY for numbers:
=SUM(--(FREQUENCY(A2:A20, A2:A20)>0))– counts unique numeric values, ignores blanks and text.
For a pivot table approach, you can add your data to a pivot table and use “Distinct Count” in the Value Field Settings (available in Excel 2013 and later). This is a no-formula solution but requires a manual refresh.
Frequently Asked Questions
How do I count unique values in a column with duplicates?
Simply use =COUNTA(UNIQUE(A2:A100)). This will give you the number of distinct entries in that range. If you have headers, start from A2.
Can I count unique values based on multiple criteria?
Yes. Use UNIQUE with FILTER and multiple conditions, e.g., =COUNTA(UNIQUE(FILTER(A2:A20, (B2:B20="West")*(C2:C20>100)))). The asterisk acts as AND.
Why does my UNIQUE function return a #SPILL! error?
This happens when there isn’t enough empty space for the results to spill. Clear cells below or to the right of the formula, or move the formula to a cell with more space.
Does UNIQUE work with merged cells?
No. UNIQUE does not handle merged cells well; it may return unexpected results. Unmerge your cells or use a different approach.
Is there a way to count unique values without a formula?
Yes, use a pivot table. Drag the field to Rows, then right-click and choose Value Field Settings > Distinct Count. This works in Excel 2013 and later.
How do I count unique values in Google Sheets?
Google Sheets has a similar UNIQUE function, but you can use =COUNTA(UNIQUE(A2:A20)) directly. Alternatively, use =COUNTUNIQUE(A2:A20), which is a dedicated function that counts unique values and ignores blanks.
Related
- Excel Eye Center Provo: Services, Doctors & What to Know
- Excel Eye Center Spanish Fork: Services, Doctors & More
- Free Excel Expense Tracking Sheet Template
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Match Excel Entries: Find Exact and Partial MatchesYes. To check whether each entry in one Excel list appears in another, put the first list in…
- Excel Split Cell by Character: Methods and FormulasTo split a cell at a repeated separator such as a comma, hyphen, or space, select the cells…
- Excel INDEX and MATCH: Find Values in Large WorksheetsIn Excel, INDEX and MATCH work together to find a value in one range and return the corresponding…
- Excel IF ISBLANK Examples: Formulas and FixesTo make Excel display one result when a cell is empty and another when it is not, combine…
- Oracle SQL Developer String Functions and ExamplesOracle SQL Developer does not have a separate set of string functions: it is the database tool where…
- XLOOKUP in Excel: How to Build and Use the FormulaTo look up a product price by ID, put the ID to find in E2, the product IDs…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.