Please consider supporting this website by buying me a coffee.
Thank you for your consideration!
Updated | November 22, 2024
Count Unique Values
The following formula returns a count of unique values in B3:B12...
=ROWS(UNIQUE(B3:B12))
To exclude blank cells from the count...
=ROWS(UNIQUE(FILTER(B3:B12,LEN(B3:B12)>0)))
To prevent the formula from returning #CALC! when there's no data available, and return 0 instead...
=IFERROR(ROWS(UNIQUE(FILTER(B3:B12,LEN(B3:B12)>0))),0)
For earlier versions of Excel...
=SUM(IF(FREQUENCY(MATCH("~"&B3:B12,B3:B12&"",0),ROW(B3:B12)-ROW(B3)+1)>0,1))
To exclude blank cells from the count...
=SUM(IF(FREQUENCY(IF(LEN(B3:B12)>0,MATCH("~"&B3:B12,B3:B12&"",0)),
ROW(B3:B12)-ROW(B3)+1)>0,1))
Note that both these formulas need to be confirmed with CTRL+SHIFT+ENTER. If done correctly, Excel will automatically place curly braces {...} around the formula.
Based on the sample data, the formula returns 3.
Sample Workbook: Download