Count unique values in Excel and Google Sheets
Count distinct values with UNIQUE, or with a helper column in older Excel.
The problem
You want how many different values there are, not how many rows.
Sample data
A2:A5 holds three distinct values across four rows.
Formulas
Excel 2021/365 and Sheets: =COUNTA(UNIQUE(A2:A5)) -> 3
Older Excel: in B2 fill down =IF(COUNTIF(A$2:A2,""&A2)=1,1,0), then =SUM(B2:B5) gives 3.
Excel vs Sheets
Sheets treats 1 and "1" as the same in UNIQUE; Excel does not.
Tested on sample data by the Cellwise checker (log CW-PL-20261018-40). AI-made: the text, voice and visuals are generated by AI.
Checked before publishing: a check.