To count unique values in Excel while ignoring duplicates, use =SUMPRODUCT(1/COUNTIF(range, range)) for a single column, or =COUNTA(UNIQUE(range)) in Excel 365/2021. The UNIQUE function is the simplest modern method, but the older SUMPRODUCT formula works in every Excel version.
What “Unique” Actually Means in Excel
Excel users often confuse two different counts. A list of unique values shows each distinct item once (e.g., Apple, Banana, Cherry from a list with repeats). A distinct count is the number of those items (3). When someone asks “count unique values not duplicates,” they usually want the distinct count—how many different entries exist, ignoring repeats.
For example, if column A contains: Red, Blue, Red, Green, Blue, Blue, the distinct count is 3 (Red, Blue, Green). The total count is 6, and the count of duplicates (extra occurrences) is 3. Excel does not have a built-in “count distinct” button, but several formulas and tools achieve it.
The UNIQUE Function (Excel 365 and 2021)
If you have Microsoft 365, Excel 2021, or Excel for the web, UNIQUE is the cleanest solution. It returns an array of unique values, and you wrap it in COUNTA to count them.
With data in A2:A20, enter:
=COUNTA(UNIQUE(A2:A20))
This returns the number of distinct text or number entries. For example, if A2:A20 contains 19 values with 4 duplicates, the formula returns 15. If any cell is empty, COUNTA ignores blanks, but UNIQUE includes a blank as a unique value if one exists—so filter blanks first or use =COUNTA(UNIQUE(FILTER(A2:A20, A2:A20<>""))).
To count unique numeric values only, use =SUM(--(FREQUENCY(A2:A20, A2:A20)>0)) but that works only for numbers. For text, the UNIQUE approach is best.
SUMPRODUCT and COUNTIF (All Versions)
For older Excel (2019 and earlier), the classic formula is:
=SUMPRODUCT(1/COUNTIF(A2:A20, A2:A20))
How it works: COUNTIF returns an array of counts for each cell—e.g., if “Red” appears 3 times, it returns 3 for each Red cell. Dividing 1 by that count gives 1/3 for each Red. Summing those fractions yields 1 per distinct value (three thirds add to 1). The result is the distinct count.
This formula fails if any cell in the range is empty, because COUNTIF counts blanks and 1/0 produces a #DIV/0! error. To handle blanks, use:
=SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20, A2:A20&""))
The &"" trick makes COUNTIF treat blanks as text, avoiding division by zero. Test it: if A2:A20 has 15 filled cells and 5 blanks, the formula returns 15 (assuming no duplicates).
Using a Pivot Table for a Distinct Count
A Pivot Table is the only built-in tool that gives a distinct count without formulas, and it works in all versions. Steps:
- Select your data range (e.g., A1:A20 with a header).
- Go to Insert > PivotTable.
- In the PivotTable Fields pane, drag the field (e.g., “Item”) to the Rows area.
- Drag the same field to the Values area.
- Click the drop-down arrow next to the field in Values, choose “Value Field Settings.”
- Select “Distinct Count” (available only when you add the field to Values, not when it’s already in Rows).
The Pivot Table shows the distinct count. Note: “Distinct Count” is not available in Excel 2013 or earlier—you’d need to add a helper column or use the Data Model (Power Pivot).
Removing Duplicates to See the Unique List
If you just want to see the unique values (not a count), use the built-in Remove Duplicates tool. Select the column, go to Data > Remove Duplicates, and check the column. Excel deletes duplicate rows, leaving only unique entries. The count of remaining rows is your distinct count.
Caution: this permanently alters your data. Always copy the column to a blank area first. For a non-destructive method, use UNIQUE in Excel 365, or apply Advanced Filter with “Unique records only” (Data > Advanced > Copy to another location).
Counting Unique Values with Multiple Criteria
Sometimes you need to count unique values based on a condition, such as “count distinct products sold in the East region.” In Excel 365, combine UNIQUE and FILTER:
With products in A2:A20 and regions in B2:B20, enter:
=COUNTA(UNIQUE(FILTER(A2:A20, B2:B20="East")))
This returns the number of distinct products where region is East. For older Excel, use a more complex array formula with FREQUENCY and MATCH, but it’s messy. A simpler workaround is to add a helper column that concatenates criteria (e.g., =A2&"-"&B2) and then count unique values in that column.
Common Problems and Fixes
- #DIV/0! error in SUMPRODUCT: caused by blank cells. Use the
&""version or filter blanks. - Spaces or invisible characters: “Red” and “Red ” count as different. Use
=TRIM(A2)in a helper column first. - Case sensitivity: COUNTIF and UNIQUE are case-insensitive by default. If you need case-sensitive, use
=SUMPRODUCT(--(FREQUENCY(MATCH(A2:A20,A2:A20,0),ROW(A2:A20)-ROW(A2)+1)>0))but that’s advanced. - UNIQUE not available: You’re on an older Excel version. Use SUMPRODUCT or a Pivot Table.
- Counting across multiple columns: To count unique pairs from two columns, use
=COUNTA(UNIQUE(A2:A20&"-"&B2:B20))in Excel 365, but this returns the count of combined strings, not individual values.
Frequently Asked Questions
How do I count unique values in a column with blanks?
Use =COUNTA(UNIQUE(FILTER(A2:A20, A2:A20<>""))) in Excel 365. In older versions, use =SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20, A2:A20&"")). Both ignore empty cells.
Why does my SUMPRODUCT formula return a fraction?
If you see 1.5 or 2.333, it’s because your range includes blank cells or the formula is not array-entered correctly. The &"" fix resolves blanks. Also, ensure you press Enter normally—SUMPRODUCT handles arrays without Ctrl+Shift+Enter.
Can I count unique values with a condition in Excel 2019?
Yes, but it’s not simple. Use a helper column: in C2 enter =IF(B2="East", A2, "") and drag down. Then count unique non-blank values in C using =SUMPRODUCT((C2:C20<>"")/COUNTIF(C2:C20, C2:C20&"")).
What’s the difference between COUNTIF and COUNTIFS for unique counts?
COUNTIF counts cells that meet one condition; COUNTIFS counts cells that meet multiple conditions. Neither counts unique values directly—they count occurrences. To count unique, you must combine them with array formulas or use UNIQUE.
Does Remove Duplicates affect my formulas?
Yes, Remove Duplicates deletes rows, which can break formulas referencing those cells. It also permanently removes data. Use it on a copy, or use UNIQUE/Advanced Filter to avoid data loss.
Is there a keyboard shortcut to count unique values?
No. But you can quickly see unique values by selecting the column and pressing Alt+A+M (Data > Remove Duplicates) after copying to a new column. Or use the status bar: select the range, and the count of unique values is not shown—only the count of numeric cells. So no shortcut exists.
Related
- Java String Built-In Methods: Uses, Examples, and Tips
- Java String Reverse: Methods and Examples
- String in Java: Meaning, Methods, and Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- EDATE Function in Excel: Add Months to DatesIn Excel, EDATE adds or subtracts a specified number of whole months from a date. With a starting…
- Excel Formula for Time Clock Hours In and Out by ShiftFor clock-in time in A2 and clock-out time in B2, enter =MOD(B2-A2,1)*24 to return the shift length as…
- How to Differentiate Log Functions Using the Chain RuleTo differentiate a logarithm of a function, use the chain rule: the derivative of ln(u) is u′/u. For…
- Excel Trim a Range: Remove Extra Spaces from CellsTo trim a range in Excel, enter =TRIM(A2) beside the first value and fill the formula down; in…
- How to Copy Unique Values in Excel Without DuplicatesTo copy a deduplicated list in Microsoft Excel, enter the UNIQUE formula in an empty cell, then copy…
- Excel SUMIF Between Dates: Multiple Criteria ExplainedTo sum values in Excel for Microsoft 365 that fall between two dates and meet other conditions, use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.