Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to count distinct values in Excel and Google Sheets
Excel & Google Sheets
=SUMPRODUCT(1/COUNTIF(A2:A6,A2:A6))Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Select the output cell.
- 2Type =SUMPRODUCT(1/COUNTIF(A2:A6,A2:A6)) and press Enter.
- 3Each unique value contributes 1/n to the sum (where n is its frequency), so duplicates net to 1 per unique value.
- 4If the range may contain blanks, protect against division by zero: =SUMPRODUCT((A2:A6<>"")/COUNTIF(A2:A6,A2:A6&"")).
- 5In Excel 365 / Google Sheets, you can also use =COUNTA(UNIQUE(A2:A6)) for a clearer formula.
Tips
- The 1/COUNTIF trick fails if there are blank cells because COUNTIF counts blanks as 0, causing a division error — use the blank-safe variant.
- In Excel 365+, =ROWS(UNIQUE(A2:A6)) is simpler and handles blanks natively.
- In Google Sheets, =COUNTUNIQUE(A2:A6) is a dedicated built-in function.
- For case-sensitive distinct counts, use EXACT inside a SUMPRODUCT array formula.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Is there a COUNTUNIQUE function in Excel?
Not in traditional Excel. Google Sheets has =COUNTUNIQUE(). In Excel 365 use =ROWS(UNIQUE(range)).
Why do I get a #DIV/0! error?
There is a blank cell in your range. Use the blank-safe formula: =SUMPRODUCT((A2:A6<>"")/COUNTIF(A2:A6,A2:A6&"")).