How to count the number of unique values in a range
✓ Verified in LibreOffice 25.8.7.3Count how many distinct values a column contains (not the total count, the number of different ones).
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =COUNTA(UNIQUE(A2:A9)) | Excel 365. Older versions: =SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9)) (no blanks). |
| Google Sheets | =COUNTUNIQUE(A2:A9) | Google Sheets has a dedicated COUNTUNIQUE; =COUNTA(UNIQUE(...)) also works. |
| LibreOffice Calc | =COUNTA(UNIQUE(A2:A9)) | LibreOffice 25.8 (UNIQUE arrived in 24.8). |
How it works
UNIQUE returns the list of distinct values; wrapping it in COUNTA counts how many there are. Here apple/banana/cherry gives 3. Google Sheets also offers the one-step COUNTUNIQUE.
Verified, not just documented
We ran =COUNTA(UNIQUE(A2:A6)) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 3 — exactly the expected result. Every formula here is confirmed by actually executing it.