Count unique values
=COUNTUNIQUE(B2:B12)Google Sheets · result on the sample sheet: 7
Describe what you need and get a working COUNTUNIQUE formula for Google Sheets, tested on real data before you copy it. COUNTUNIQUE counts how many different values appear in a range, ignoring blank cells.
Each formula below was calculated on its own sample sheet. Open one to see the sheet, why it works, and to try it yourself.
=COUNTUNIQUE(B2:B12)Google Sheets · result on the sample sheet: 7
=COUNTUNIQUE(FILTER(B2:B12,C2:C12=F1))Google Sheets · result on the sample sheet: 3
=COUNTA(UNIQUE(A2:A10))Excel · result on the sample sheet: 5
=COUNTA(UNIQUE(TOCOL(B2:D6,1)))Excel · result on the sample sheet: 7
You describe the task in plain English, and it writes the formula, using COUNTUNIQUE where that's the right tool, for your table or a sample one. The formula is run in our engine in Google Sheets mode before you see it, so you get its result, not just a guess.
COUNTUNIQUE counts how many different values appear in a range, ignoring blank cells.
Yes. Each example below opens a page where you try the formula in a live grid and it's checked as you go, and the COUNTUNIQUE function page has a practice exercise.