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 without a pivot table in Excel and Google Sheets
Topic:Pivot-style summaries (formula approach)Heads up: Excel and Google Sheets do this differently.
Excel
=SUMPRODUCT(1/COUNTIF(A2:A6,A2:A6))Google Sheets
=COUNTUNIQUE(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
- 1Place the values you want to count (possibly with duplicates) in A2:A6.
- 2In Excel, enter =SUMPRODUCT(1/COUNTIF(A2:A6,A2:A6)). COUNTIF returns how many times each value appears; 1/count gives each occurrence a fractional weight summing to 1 per distinct value.
- 3SUMPRODUCT adds all fractions, yielding the total distinct count.
- 4In Google Sheets, use =COUNTUNIQUE(A2:A6) for the same result with cleaner syntax.
- 5To count distinct values meeting a condition (e.g., Region = 'East'), use =SUMPRODUCT((B2:B6="East")/COUNTIFS(A2:A6,A2:A6,B2:B6,B2:B6)).
Tips
- SUMPRODUCT(1/COUNTIF) breaks if the range contains blank cells — wrap with: =SUMPRODUCT((A2:A6<>"")/COUNTIF(A2:A6,A2:A6&"")) to guard against division by zero.
- Excel 365 users can also use =ROWS(UNIQUE(A2:A6)) for a simpler distinct count.
- For a conditional distinct count in Google Sheets, use COUNTUNIQUE(FILTER(A2:A6,B2:B6="East")).
Need the Google Sheets version instead? Open Sheets variant in workspace →
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
Why does SUMPRODUCT(1/COUNTIF) divide by zero?
If the range contains empty cells, COUNTIF returns 0 for them, causing division by zero. Add a blank check: =SUMPRODUCT((A2:A6<>"")*1/COUNTIF(A2:A6,A2:A6&"")).
How do I get a distinct count by group (like a pivot's Distinct Count in Values)?
Use =SUMPRODUCT((A2:A6="East")/COUNTIFS(B2:B6,B2:B6,A2:A6,A2:A6)) where A is the group and B is the value column.
Is COUNTUNIQUE available in Excel?
No. COUNTUNIQUE is Google Sheets only. In Excel use SUMPRODUCT(1/COUNTIF) or ROWS(UNIQUE(...)) in Excel 365.
More on Pivot-style summaries (formula approach)
See all →Reference
GETPIVOTDATA
Extract a specific value from a PivotTable using field names and item values as explicit identifiers.
How-toHow to build a pivot-style summary with formulas
Build a formula-driven pivot-style cross-tab in Excel or Google Sheets using SUMIFS with mixed references — live totals by row and column without inserting a pivot table.
How-toHow to build a pivot-style summary with QUERY
Use Google Sheets QUERY with GROUP BY, SUM, and LABEL to build a pivot-style summary table without a manual pivot table — Excel alternative uses SUMIFS.
How-toHow to use GETPIVOTDATA to pull values from a pivot table
Learn how GETPIVOTDATA extracts specific values from a pivot table in Excel and Google Sheets, avoiding broken references when the pivot refreshes.
How-toHow to write formulas for a pivot table calculated field
Understand how pivot calculated fields work and when to replace them with SUMIFS-based formulas in Excel and Google Sheets for more control and flexibility.
How-toHow to calculate percent of total without a pivot table
Use SUMIF and SUM to calculate each category's percent of grand total in Excel and Google Sheets — no pivot table required.