Count Unique Values in Excel: UNIQUE & COUNTA

Formulas & functions · Updated November 22, 2026

To count unique values in a list in Microsoft Excel, use the COUNTA function wrapped around the UNIQUE function: =COUNTA(UNIQUE(range)). For example, if your data is in A2:A10, the formula =COUNTA(UNIQUE(A2:A10)) returns the number of distinct entries, ignoring blanks and duplicates.

Why the Simple Formula Works

The UNIQUE function, available in Excel 365 and Excel 2021, extracts a list of distinct values from a range. When you wrap it in COUNTA, you count the non-empty cells in that extracted list. Because UNIQUE automatically removes duplicates and omits blank cells, the result is exactly the number of unique items.

For a quick test: enter Apple, Banana, Apple, Cherry in A1:A4. The formula =COUNTA(UNIQUE(A1:A4)) returns 3. If you add a blank cell in A5, the result stays 3 because blanks are ignored.

Counting Unique Values with Conditions

Often you need to count unique values only if they meet a certain condition, such as counting distinct products sold in a specific region. The classic approach uses SUMPRODUCT with COUNTIFS:

=SUMPRODUCT((range_condition=criteria)/COUNTIFS(range_values, range_values, range_condition, criteria))

For example, with products in A2:A20 and regions in B2:B20, to count unique products in the “West” region, use:

=SUMPRODUCT((B2:B20="West")/COUNTIFS(A2:A20, A2:A20, B2:B20, "West"))

This works by dividing 1 by the number of times each product appears in the West region, then summing those fractions. A product appearing twice contributes 0.5 + 0.5 = 1, so each unique product adds exactly 1.

In Excel 365, a simpler alternative uses UNIQUE with FILTER:

=COUNTA(UNIQUE(FILTER(A2:A20, B2:B20="West")))

This filters the product list to only West rows, then counts unique values. It is easier to read and less prone to division-by-zero errors.

Using the UNIQUE Function for a Dynamic List

If you want to see the actual unique values, not just the count, use UNIQUE alone. For example, =UNIQUE(A2:A20) spills a list of distinct values down the column. You can then use COUNTA on that spilled range, but be careful: if the spilled range is in C2, use =COUNTA(C2#) to reference the entire spill. The # operator ensures you count all values even if the list length changes.

This dynamic approach is ideal when your data updates frequently—the count and list update automatically without needing to adjust ranges.

Counting Unique Values with Blanks or Errors

Blanks are automatically excluded by UNIQUE, but what if you want to count blanks as a category? Use COUNTA(UNIQUE(range)) will ignore blanks, so if you have a list with one blank and three unique text entries, the result is 3. If you need to include blank as a unique value, you can use ROWS(UNIQUE(range)) but that also ignores blanks. To include blanks, use SUMPRODUCT with FREQUENCY on the numeric representation, but that is complex. For most purposes, ignoring blanks is the desired behavior.

Errors in the range (like #N/A) will cause UNIQUE to return an error. To handle errors, wrap the range in IFERROR before applying UNIQUE: =COUNTA(UNIQUE(IFERROR(range,""))). This converts errors to text strings, which are then counted as unique values if they appear.

Alternative: PivotTable for a Quick Count

If you prefer a visual approach, a PivotTable can count unique values without formulas. Create a PivotTable from your data, drag the field you want to count into the Values area, and change the value field settings to “Distinct Count”. This option is available in Excel 2013 and later. It is especially useful when you need to see unique counts broken down by categories, such as unique customers per month.

To do this: select your data, go to Insert > PivotTable, drag the field to Rows and another field to Values, then right-click the Values field > Value Field Settings > Summarize by > Distinct Count. This method is manual but requires no formulas and updates when you refresh the PivotTable.

Common Mistakes and How to Avoid Them

One frequent error is using COUNTIF to count unique values, which only counts cells that meet a single criterion, not distinct values. For example, =COUNTIF(A2:A20,"Apple") counts how many times “Apple” appears, not how many unique items exist. Another mistake is forgetting that UNIQUE is not available in older Excel versions; if you use Excel 2019 or earlier, you must use the SUMPRODUCT method or a PivotTable.

Also, when using SUMPRODUCT with COUNTIFS, ensure the ranges are the same size and that you use absolute references if copying the formula. A common error is dividing by zero when a criterion has no matches; the SUMPRODUCT formula will return a #DIV/0! error. To avoid this, you can wrap the formula in IFERROR to return 0 when no matches exist.

Frequently Asked Questions

How do I count unique values in a column with multiple criteria? Use the SUMPRODUCT method with multiple COUNTIFS conditions. For example, to count unique products in the West region with sales over $100, use =SUMPRODUCT((B2:B20="West")*(C2:C20>100)/COUNTIFS(A2:A20,A2:A20,B2:B20,"West",C2:C20,">100")). This divides 1 by the count of each product meeting both criteria.

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 use the implicit intersection operator @ to return only the first value, but that defeats the purpose. Ensure the target area is empty.

Can I count unique values in Excel for the web? Yes, Excel for the web supports UNIQUE and COUNTA as long as you are using a Microsoft 365 subscription. The formulas work the same as in the desktop version.

Is there a keyboard shortcut to insert the UNIQUE function? No, but you can type =UNIQUE( and then select the range. On Windows, press Ctrl+Shift+Enter is not needed for dynamic array functions; just press Enter.

How do I count unique values ignoring case? Excel’s UNIQUE is case-sensitive by default, so “Apple” and “apple” are considered different. To ignore case, use =COUNTA(UNIQUE(LOWER(range))) to convert all to lowercase first. This works in Excel 365.

What is the difference between COUNTIF and COUNTIFS? COUNTIF counts cells that meet a single condition, while COUNTIFS counts cells that meet multiple conditions. Neither counts unique values directly; they count occurrences. For unique counts, you need UNIQUE or the SUMPRODUCT method.

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.