Count Unique Values in Excel
Count how many distinct values a range contains, using UNIQUE in modern Excel or a SUMPRODUCT formula in older versions.
UNIQUE
Count how many distinct values a range contains, using UNIQUE in modern Excel or a SUMPRODUCT formula in older versions.
Column of values to de-duplicate. Trim it to the rows you actually use - blank cells add an extra entry.
=COUNTA(UNIQUE(A2:A100))Worked Example
A2:A11 lists 10 order IDs for the day, three of which repeat an ID already in the list.
=COUNTA(UNIQUE(A2:A11))Returns: 7 - the number of distinct order IDs among the 10 rows.
Checks Before You Paste
- •No UNIQUE function in your Excel? On Excel 2019 and earlier the classic replacement is =SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100)), which gives the same distinct count. It returns #DIV/0! if any cell in the range is blank, so the blank-safe form is =SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&"")).
- •In Excel, blank cells inside the range make UNIQUE emit a 0, so the count comes back one higher than expected. Either size the range to the filled rows or use =COUNTA(UNIQUE(FILTER(A2:A100,A2:A100<>""))), which drops the blanks before counting and works the same way in Google Sheets.
- •The legacy SUMPRODUCT version compares every cell against every other cell, so never point it at a whole column like A:A - a million rows will lock the sheet up. UNIQUE is far cheaper, which is another reason to move the formula over when the file allows it.