Excel Function for Unique Count: UNIQUE & COUNTA

Formulas & functions · Updated November 22, 2026

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”:

  1. In a cell, enter =COUNTA(UNIQUE(FILTER(A2:A20, B2:B20="West"))).
  2. 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

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.