The short answer: Excel’s COUNTIF function does not remove duplicates, but you can combine it with other functions like SUMPRODUCT, or use the built-in Remove Duplicates tool, to count only unique values. The simplest formula to count unique items in a range is =SUMPRODUCT(1/COUNTIF(range, range)), which works in all Excel versions.
What COUNTIF Actually Does
COUNTIF is a statistical function that counts cells meeting a single condition, such as =COUNTIF(A2:A20, "Apple"). It returns the number of times “Apple” appears, including every duplicate. It does not filter or deduplicate data—it only tallies matches. If you need to remove duplicates from your data (physically delete repeated rows), COUNTIF is not the tool; you would use the Remove Duplicates command or Power Query.
Why You Might Need to Count Without Duplicates
Counting unique values is a common need when analyzing survey responses, sales transactions, or inventory lists. For example, if you have a list of customer orders, you might want to know how many distinct customers placed orders, not how many order lines exist. Using COUNTIF alone would count each order, including multiple orders from the same customer. To get a true unique count, you need a different approach.
Formula to Count Unique Values (No Duplicates)
The classic formula uses SUMPRODUCT with COUNTIF:
Assuming your data is in A2:A20 (with no blank cells), enter:
=SUMPRODUCT(1/COUNTIF(A2:A20, A2:A20))
How it works: COUNTIF returns an array of counts for each cell. For example, if “Apple” appears 3 times, the array contains 3 for each occurrence. Dividing 1 by that count gives 1/3 for each. SUMPRODUCT adds those fractions: three 1/3s sum to 1, so each unique item contributes exactly 1. The result is the number of unique items. For a list like Apple, Apple, Banana, it returns 2.
If your range contains blank cells, the formula returns a #DIV/0! error because COUNTIF counts blanks as 0. To handle blanks, use this array formula (enter with Ctrl+Shift+Enter in older Excel, or just Enter in Excel 365):
=SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20, A2:A20&""))
Using UNIQUE Function (Excel 365 and 2021)
If you have Excel 365 or Excel 2021, the UNIQUE function is the modern way. It returns a list of unique values directly. To get a count, wrap it in COUNTA:
With data in A2:A20, use:
=COUNTA(UNIQUE(A2:A20))
This returns the number of unique entries, ignoring blanks (UNIQUE excludes blanks by default). If you want to count unique values that appear only once (i.e., no duplicates at all), use:
=COUNTA(UNIQUE(A2:A20, , TRUE))
The third argument TRUE returns items that occur exactly once.
How to Remove Duplicates (The Tool, Not the Formula)
If you actually need to delete duplicate rows from your data, use the built-in command:
- Select the range or click any cell in your data.
- Go to the Data tab.
- Click Remove Duplicates (in the Data Tools group).
- In the dialog, choose which columns to check for duplicates.
- Click OK. Excel will remove duplicate rows and show a message like “X duplicate values removed; Y unique values remain.”
This permanently deletes rows, so save a copy of your workbook first. The shortcut is Alt+A+M (Windows) or Control+Command+R on Mac (if you have the full ribbon).
For a non-destructive method, use Power Query: Data > From Table/Range, then in Power Query Editor, right-click the column and choose Remove Duplicates, then Close & Load. This creates a new table without altering your original data.
Common Problems and Solutions
- #DIV/0! error in the SUMPRODUCT formula: This happens when the range contains blank cells. Use the blank-safe version above or ensure no blanks.
- Counting unique with conditions: If you want to count unique items that meet a criterion (e.g., only “Apple” customers in a region), use a more complex array formula. For Excel 365, use
=COUNTA(UNIQUE(FILTER(A2:A20, B2:B20="West")))where B is the region column. - Case sensitivity: COUNTIF and UNIQUE are case-insensitive by default. If you need case-sensitive unique counts, you’d need a helper column with EXACT or use a VBA macro.
- Large datasets: The SUMPRODUCT formula can be slow on thousands of rows. For large data, use UNIQUE (if available) or a PivotTable.
Comparing COUNTIF with Other Methods
COUNTIF is simple but limited. The Remove Duplicates tool is destructive but quick. UNIQUE is dynamic and updates automatically when source data changes. A PivotTable is another option: drag the field to Rows and it shows unique items, then you can count them. Each method has trade-offs in speed, version compatibility, and whether you need a live result.
Frequently Asked Questions
Can I use COUNTIF to remove duplicates? No, COUNTIF only counts. To remove duplicates, use the Remove Duplicates command or Power Query.
Why does my SUMPRODUCT formula return a #DIV/0! error? Because your range has blank cells. Use the blank-safe version: =SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20, A2:A20&"")).
What’s the difference between COUNTIF and COUNTIFS? COUNTIF handles one condition; COUNTIFS handles multiple conditions. Neither removes duplicates.
How do I count unique values in Excel 2019? Use the SUMPRODUCT formula above, since UNIQUE is not available in Excel 2019.
Does the UNIQUE function work on Mac? Yes, UNIQUE works in Excel for Microsoft 365 on Mac and Windows, as well as Excel for the web.
Can I count unique values with a PivotTable? Yes, add the field to Rows, then right-click and set Value Field Settings to Count. It shows unique items in the row labels, and you can count them manually or with a formula.
Related
- Java split() Usage: Syntax, Examples, and Common Traps
- Databricks SQL CONTAINS: Find Text in a String
- Split Syntax in Java: Examples and Common Pitfalls
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel VLOOKUP Multiple Values: Formulas That WorkTo return every matching row for one lookup value in Microsoft 365, use FILTER rather than VLOOKUP. With…
- Excel: How to Combine Names in Two Columns with a FormulaTo combine names in two Excel columns, enter =A2&" "&B2 in a blank cell on row 2, replacing…
- IFERROR in Excel: How to Catch and Handle Formula ErrorsTo use IFERROR in Excel, wrap a calculation or formula in IFERROR and choose what to show if…
- Excel Min Date Formula: Find Earliest DateThe simplest Excel formula to find the minimum (earliest) date in a range is =MIN(A2:A100), assuming your dates…
- Excel INDIRECT: Reference Another Workbook or FileYes. Excel’s INDIRECT function can refer to a cell in another workbook, but the source workbook must be…
- Excel Freeze Panes: Keep Top Row and Left Column VisibleTo freeze the top row and left column together in Excel for Microsoft 365 on Windows, select cell…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.