Excel Count Unique Values: 6 Ways to Ignore Duplicates

Formulas & functions · Updated November 22, 2026

To count unique values in Excel while ignoring duplicates, use =SUMPRODUCT(1/COUNTIF(range, range)) for a single column, or =COUNTA(UNIQUE(range)) in Excel 365/2021. The UNIQUE function is the simplest modern method, but the older SUMPRODUCT formula works in every Excel version.

What “Unique” Actually Means in Excel

Excel users often confuse two different counts. A list of unique values shows each distinct item once (e.g., Apple, Banana, Cherry from a list with repeats). A distinct count is the number of those items (3). When someone asks “count unique values not duplicates,” they usually want the distinct count—how many different entries exist, ignoring repeats.

For example, if column A contains: Red, Blue, Red, Green, Blue, Blue, the distinct count is 3 (Red, Blue, Green). The total count is 6, and the count of duplicates (extra occurrences) is 3. Excel does not have a built-in “count distinct” button, but several formulas and tools achieve it.

The UNIQUE Function (Excel 365 and 2021)

If you have Microsoft 365, Excel 2021, or Excel for the web, UNIQUE is the cleanest solution. It returns an array of unique values, and you wrap it in COUNTA to count them.

With data in A2:A20, enter:

=COUNTA(UNIQUE(A2:A20))

This returns the number of distinct text or number entries. For example, if A2:A20 contains 19 values with 4 duplicates, the formula returns 15. If any cell is empty, COUNTA ignores blanks, but UNIQUE includes a blank as a unique value if one exists—so filter blanks first or use =COUNTA(UNIQUE(FILTER(A2:A20, A2:A20<>""))).

To count unique numeric values only, use =SUM(--(FREQUENCY(A2:A20, A2:A20)>0)) but that works only for numbers. For text, the UNIQUE approach is best.

SUMPRODUCT and COUNTIF (All Versions)

For older Excel (2019 and earlier), the classic formula is:

=SUMPRODUCT(1/COUNTIF(A2:A20, A2:A20))

How it works: COUNTIF returns an array of counts for each cell—e.g., if “Red” appears 3 times, it returns 3 for each Red cell. Dividing 1 by that count gives 1/3 for each Red. Summing those fractions yields 1 per distinct value (three thirds add to 1). The result is the distinct count.

This formula fails if any cell in the range is empty, because COUNTIF counts blanks and 1/0 produces a #DIV/0! error. To handle blanks, use:

=SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20, A2:A20&""))

The &"" trick makes COUNTIF treat blanks as text, avoiding division by zero. Test it: if A2:A20 has 15 filled cells and 5 blanks, the formula returns 15 (assuming no duplicates).

Using a Pivot Table for a Distinct Count

A Pivot Table is the only built-in tool that gives a distinct count without formulas, and it works in all versions. Steps:

  1. Select your data range (e.g., A1:A20 with a header).
  2. Go to Insert > PivotTable.
  3. In the PivotTable Fields pane, drag the field (e.g., “Item”) to the Rows area.
  4. Drag the same field to the Values area.
  5. Click the drop-down arrow next to the field in Values, choose “Value Field Settings.”
  6. Select “Distinct Count” (available only when you add the field to Values, not when it’s already in Rows).

The Pivot Table shows the distinct count. Note: “Distinct Count” is not available in Excel 2013 or earlier—you’d need to add a helper column or use the Data Model (Power Pivot).

Removing Duplicates to See the Unique List

If you just want to see the unique values (not a count), use the built-in Remove Duplicates tool. Select the column, go to Data > Remove Duplicates, and check the column. Excel deletes duplicate rows, leaving only unique entries. The count of remaining rows is your distinct count.

Caution: this permanently alters your data. Always copy the column to a blank area first. For a non-destructive method, use UNIQUE in Excel 365, or apply Advanced Filter with “Unique records only” (Data > Advanced > Copy to another location).

Counting Unique Values with Multiple Criteria

Sometimes you need to count unique values based on a condition, such as “count distinct products sold in the East region.” In Excel 365, combine UNIQUE and FILTER:

With products in A2:A20 and regions in B2:B20, enter:

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

This returns the number of distinct products where region is East. For older Excel, use a more complex array formula with FREQUENCY and MATCH, but it’s messy. A simpler workaround is to add a helper column that concatenates criteria (e.g., =A2&"-"&B2) and then count unique values in that column.

Common Problems and Fixes

  • #DIV/0! error in SUMPRODUCT: caused by blank cells. Use the &"" version or filter blanks.
  • Spaces or invisible characters: “Red” and “Red ” count as different. Use =TRIM(A2) in a helper column first.
  • Case sensitivity: COUNTIF and UNIQUE are case-insensitive by default. If you need case-sensitive, use =SUMPRODUCT(--(FREQUENCY(MATCH(A2:A20,A2:A20,0),ROW(A2:A20)-ROW(A2)+1)>0)) but that’s advanced.
  • UNIQUE not available: You’re on an older Excel version. Use SUMPRODUCT or a Pivot Table.
  • Counting across multiple columns: To count unique pairs from two columns, use =COUNTA(UNIQUE(A2:A20&"-"&B2:B20)) in Excel 365, but this returns the count of combined strings, not individual values.

Frequently Asked Questions

How do I count unique values in a column with blanks?

Use =COUNTA(UNIQUE(FILTER(A2:A20, A2:A20<>""))) in Excel 365. In older versions, use =SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20, A2:A20&"")). Both ignore empty cells.

Why does my SUMPRODUCT formula return a fraction?

If you see 1.5 or 2.333, it’s because your range includes blank cells or the formula is not array-entered correctly. The &"" fix resolves blanks. Also, ensure you press Enter normally—SUMPRODUCT handles arrays without Ctrl+Shift+Enter.

Can I count unique values with a condition in Excel 2019?

Yes, but it’s not simple. Use a helper column: in C2 enter =IF(B2="East", A2, "") and drag down. Then count unique non-blank values in C using =SUMPRODUCT((C2:C20<>"")/COUNTIF(C2:C20, C2:C20&"")).

What’s the difference between COUNTIF and COUNTIFS for unique counts?

COUNTIF counts cells that meet one condition; COUNTIFS counts cells that meet multiple conditions. Neither counts unique values directly—they count occurrences. To count unique, you must combine them with array formulas or use UNIQUE.

Does Remove Duplicates affect my formulas?

Yes, Remove Duplicates deletes rows, which can break formulas referencing those cells. It also permanently removes data. Use it on a copy, or use UNIQUE/Advanced Filter to avoid data loss.

Is there a keyboard shortcut to count unique values?

No. But you can quickly see unique values by selecting the column and pressing Alt+A+M (Data > Remove Duplicates) after copying to a new column. Or use the status bar: select the range, and the count of unique values is not shown—only the count of numeric cells. So no shortcut exists.

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.