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 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
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.