How to count the number of unique values in a range
✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)Count 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. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 3 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run Excel.
Functions used
COUNTA · UNIQUE · COUNTUNIQUE — see full Excel, Google Sheets & LibreOffice compatibility for each.
Related recipes
- How to auto-number rows with a formula
- How to combine two lists without duplicates
- How to count non-blank (non-empty) cells
- How to count unique values that match a condition
- How to get unique values (remove duplicates) with a formula