Count Unique Values in a Column

Excel & Google Sheets

How it works

The universal version is a classic trick: COUNTIF(A2:A7,A2:A7) counts how often each entry appears, and 1 divided by that count makes every copy of an entry contribute a fraction summing to exactly 1 — three Acmes contribute ⅓ + ⅓ + ⅓. SUMPRODUCT adds the fractions, giving the number of distinct entries. The modern platforms have cleaner answers: Excel 365 spills the distinct list with UNIQUE and counts it with ROWS; Google Sheets has a dedicated COUNTUNIQUE.

COUNTIF(A2:A7,A2:A7)

For every row, how many times its value appears in the range.

1/…

Turns each group of duplicates into fractions that total exactly 1.

SUMPRODUCT(…)

Adds the fractions — the result is the count of distinct values.

When to use it

Use it for distinct customers in an order log, different products sold this month, unique error codes in an export — any "how many different X" question.

Common mistakes

• ✕A blank cell anywhere in the range.

  ✓COUNTIF of a blank is 0, and 1/0 blows up the whole formula with #DIV/0!. Restrict the range to filled rows, or use =SUMPRODUCT((A2:A7<>"")/COUNTIF(A2:A7,A2:A7&"")).

• ✕Counting rows when you meant distinct values.

  ✓COUNTA counts every filled row including duplicates. If Acme ordered three times, COUNTA says 3; a unique count says 1.

• ✕Using UNIQUE in Excel 2019 or older.

  ✓UNIQUE needs Excel 365/2021 — older versions show #NAME?. Fall back to the SUMPRODUCT version, which works everywhere.