To count unique values in Excel while excluding duplicates, use =SUMPRODUCT(1/COUNTIF(range, range)) (for numbers and text) or =COUNTA(UNIQUE(range)) in Excel 365/2021. The first formula counts each distinct item once; the second returns a dynamic array of unique values and counts them.
Why COUNTIF Alone Counts Duplicates
The standard COUNTIF function counts every cell that meets a condition, including repeated values. For example, if column A contains Apple, Apple, Banana, Cherry, =COUNTIF(A:A,"Apple") returns 2, not 1. This is by design—COUNTIF is a conditional counter, not a unique counter. To exclude duplicates, you need a formula that divides each count by itself, effectively turning each duplicate group into a single 1.
The Classic Formula: SUMPRODUCT(1/COUNTIF)
Assume your data is in cells A2:A10 (adjust the range to fit your data). Enter this formula in any empty cell:
=SUMPRODUCT(1/COUNTIF(A2:A10, A2:A10))
How it works: COUNTIF returns an array of counts for each cell—e.g., for Apple, Apple, Banana, it returns {2;2;1}. Dividing 1 by each count gives {0.5;0.5;1}. Summing those fractions yields 2, which is the number of unique items. This formula works for text, numbers, and mixed data, but it can be slow on very large datasets (over ~10,000 rows) because it performs a full range calculation for each cell.
Modern Alternative: UNIQUE Function (Excel 365/2021)
If you have Excel for Microsoft 365, Excel 2021, or Excel for the web, the UNIQUE function is simpler and faster. To count unique values in A2:A10, use:
=COUNTA(UNIQUE(A2:A10))
This returns the number of distinct items. If your range contains blank cells, UNIQUE includes a blank as a unique value, so you may want to filter them out: =COUNTA(UNIQUE(FILTER(A2:A10, A2:A10<>""))). On Mac, the formula is identical; the function is available in Excel 2021 and later.
Counting Unique Values with Conditions
Sometimes you need to count unique values that meet a criterion, such as unique products sold by a specific salesperson. Use the SUMPRODUCT approach with a condition. For example, with salespersons in A2:A20 and products in B2:B20, to count unique products sold by “John”:
=SUMPRODUCT((A2:A20="John") / COUNTIFS(B2:B20, B2:B20, A2:A20, A2:A20))
This divides 1 by the count of each product within John’s rows, then sums. A simpler method in Excel 365: =COUNTA(UNIQUE(FILTER(B2:B20, A2:A20="John"))). The latter is easier to read and maintain.
Handling Blank Cells and Errors
The SUMPRODUCT(1/COUNTIF) formula returns a #DIV/0! error if the range contains any blank cells, because COUNTIF counts blanks as 0. To avoid this, use an array formula that excludes blanks:
=SUMPRODUCT((A2:A10<>") / COUNTIF(A2:A10, A2:A10))
This only evaluates non-blank cells. Alternatively, use UNIQUE with FILTER as shown above. If your data contains errors (like #N/A), COUNTIF will ignore them, but UNIQUE will include them; you can wrap with IFERROR if needed.
Comparing Methods: Which One Should You Use?
- SUMPRODUCT(1/COUNTIF): Works in all Excel versions, but slower on large data and fails with blanks.
- COUNTA(UNIQUE()): Fast, dynamic, and easy to read, but requires Excel 365/2021 or Excel for the web.
- PivotTable: A non-formula option—drag the field to Rows and use “Count Distinct” (available in Excel 2013+ as a data model feature) or just count the row labels.
- Remove Duplicates: A one-time data cleanup, not a formula. Use Data > Remove Duplicates, then count the remaining rows.
For most users, COUNTA(UNIQUE()) is the best choice if available. If you need compatibility with older Excel, stick with SUMPRODUCT.
Frequently Asked Questions
How do I count unique values in Excel with multiple criteria?
Use SUMPRODUCT with COUNTIFS, as shown above, or in Excel 365 use COUNTA(UNIQUE(FILTER(range, (criteria1)*(criteria2)))). For example, to count unique products sold by John in the East region: =COUNTA(UNIQUE(FILTER(B2:B20, (A2:A20="John")*(C2:C20="East")))).
Why does my COUNTIF formula count duplicates?
Because COUNTIF counts every cell that matches the criteria, not distinct values. It has no built-in unique mode. To exclude duplicates, you must combine it with other functions or use UNIQUE.
Can I use COUNTIF with a helper column to exclude duplicates?
Yes. Add a helper column with a formula like =IF(COUNTIF($A$2:A2, A2)=1, 1, 0) to mark the first occurrence of each value, then sum that column. This is a common workaround in older Excel versions.
Does the UNIQUE function work in Excel 2019?
No. UNIQUE was introduced in Excel 365 and Excel 2021. In Excel 2019, you must use SUMPRODUCT or a helper column.
How do I count unique values in a pivot table?
In Excel 2013 and later, create a pivot table, add the field to Rows, then add the same field to Values and change the value field settings to “Distinct Count” (requires adding the data to the Data Model). Alternatively, just count the number of row labels manually.
Common Mistakes and Troubleshooting
One common mistake is using =COUNTIF(A:A, A:A) expecting a unique count—it returns the count for the first cell only. Another is forgetting to press Ctrl+Shift+Enter for array formulas in older Excel versions; if you use SUMPRODUCT, it handles arrays natively, but if you use {=SUM(1/COUNTIF(...))}, you must confirm with Ctrl+Shift+Enter. Also, be aware that COUNTIF is not case-sensitive, so “apple” and “Apple” are treated as the same value. If case matters, you’d need a more complex array formula with EXACT.
Related
- Snowflake SPLIT_PART: Syntax, Examples, and Edge Cases
- Java String Built-In Methods: Uses, Examples, and Tips
- Java String Reverse: Methods and Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- C++ Sort for Multiple Strings: Syntax and ExamplesTo sort multiple strings in C++, put them in a container such as std::vector<std::string> and call std::sort(strings.begin(), strings.end()),…
- Daily Timesheet Template in Excel: Setup and FormulasA practical daily timesheet template in Excel can use one row per employee per workday, with columns for…
- Rank in Excel Formula: Use RANK.EQ and Handle TiesTo find a number’s position in a list in Microsoft Excel, use RANK.EQ. For example, if scores are…
- How to Filter Highest to Lowest in Excel: A Quick GuideTo show the largest values first in Excel, open the filter arrow on the column you want to…
- Excel Formula Bot Free? Free Plans, Limits, and Safe UseYes—many Excel formula bots offer free access, but it is usually limited by usage, features, or account requirements.…
- Excel INDIRECT Named Range: How It Works and When to Use ItIn Excel, INDIRECT turns text into a cell or range reference, so you can use it to refer…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.