Count Unique Values in Excel (Ignore Duplicates)

If you have a list with duplicate entries and need to know how many distinct values it contains, a standard COUNT won't help — it counts every row. The formula below counts each unique value once, regardless of how many times it appears.

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

In Excel 2019 and earlier, press Ctrl+Shift+Enter to enter this as an array formula. In Excel 365 and Excel 2021, press Enter normally.

How It Works

COUNTIF here counts each value against the full list. For a country that appears 4 times, it returns 4. For one that appears once, it returns 1.

Dividing 1 by that count converts each occurrence to a fraction: a country appearing 4 times contributes 0.25 per row. Four rows × 0.25 = 1. A country appearing once contributes 1.0 directly. SUM adds all the fractions together, and the result is the number of distinct values.

With 19 entries across 7 countries, the formula returns 7.

The Same Result Without an Array Formula

If you'd rather not deal with Ctrl+Shift+Enter, SUMPRODUCT handles the array logic automatically:

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

Same logic, same result. Enter it with a plain Enter. This is the version to use if the file will be shared with people on older Excel versions or if you want to avoid the {} brackets causing confusion.

If the List Contains Blank Cells

Both formulas above will return a #DIV/0! error if there are any blank cells in the range — COUNTIF returns 0 for blanks, and dividing by 0 breaks the formula.

Wrap the count in an IF to skip blanks:

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

// A2:A20<>""      — excludes blank cells from the sum
// COUNTIF(...&"") — prevents division by zero on blank rows

If You're on Excel 365

Excel 365 has a dedicated function for this:

=COUNTA(UNIQUE(A2:A20))

UNIQUE extracts the distinct values, COUNTA counts them. It's easier to read, handles blanks cleanly, and doesn't require any special entry. Use SUMPRODUCT(1/COUNTIF(...)) only if you need the workbook to work on older Excel versions.