Use `=SUM(1/COUNTIF(range,range))` as an array formula in older Excel versions
Use `=SUMPRODUCT((range<>“”)/COUNTIF(range,range&””))` for a non-array formula
Use `=COUNTA(UNIQUE(range))` in Excel 365 or Excel 2021
Use a PivotTable and add the field to Values, then choose Distinct Count
Use `=SUM(–(FREQUENCY(MATCH(range,range,0),MATCH(range,range,0))>0))` for unique numeric values
Use `Remove Duplicates` on a copied range, then count the remaining cells
Use `=COUNTIF(range,criteria)` with helper columns if counting distinct values by condition
Use `=SUMPRODUCT((range<>“”)/COUNTIF(range,range&””))` to exclude blanks
Use `=COUNTA(UNIQUE(FILTER(range,condition)))` to count distinct values with a filter condition
Use `=ROWS(UNIQUE(range))` to count distinct values directly in newer Excel versions