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.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
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&"")).