The short answer: Excel’s COUNTIF function does not have a built-in option to ignore duplicates, but you can count only unique values by combining SUMPRODUCT with COUNTIF, or by using the newer UNIQUE function. For example, to count unique items in A2:A20, use =SUMPRODUCT(1/COUNTIF(A2:A20,A2:A20)) (Excel 2010 and later) or =COUNTA(UNIQUE(A2:A20)) (Excel 365/2021). This article explains why COUNTIF counts duplicates, how to work around it, and which method fits your version and data.
Why COUNTIF Counts Duplicates by Default
COUNTIF’s job is to count every cell in a range that meets a condition. If the same value appears three times, COUNTIF counts it three times—that’s by design. The function has no parameter like “unique only” because it’s a basic conditional counter. When users search for “COUNTIF ignore duplicates,” they usually want a distinct count: how many different values exist, not how many total entries.
For instance, with the list Apple, Banana, Apple, Cherry in A1:A4, =COUNTIF(A1:A4,"Apple") returns 2, not 1. To get the number of unique fruits (3), you need a different approach.
Method 1: SUMPRODUCT with COUNTIF (All Excel Versions)
The classic formula for counting unique values is =SUMPRODUCT(1/COUNTIF(range, range)). Here’s how it works:
- COUNTIF(range, range) returns an array of counts for each cell. For
Apple, Banana, Apple, Cherry, it returns{2,1,2,1}. - Dividing 1 by each count gives
{0.5,1,0.5,1}. - SUMPRODUCT adds these: 0.5+1+0.5+1 = 3, the number of unique items.
To use it, with your data in A2:A20 (excluding headers), enter:
=SUMPRODUCT(1/COUNTIF(A2:A20,A2:A20))
This works in Excel 2010 and later, including Excel 365. However, it has a catch: if the range contains any blank cells, COUNTIF returns 0 for blanks, and dividing by zero gives a #DIV/0! error. To handle blanks, use an array formula like:
=SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20,A2:A20&""))
This adds an empty string to the criteria, which makes COUNTIF treat blanks as text and avoids division by zero. But it counts blank cells as one unique value if any exist, so it’s best to keep your range free of blanks.
Method 2: UNIQUE Function (Excel 365 and 2021)
If you have Excel for Microsoft 365 or Excel 2021, the UNIQUE function is the cleanest solution. It returns a list of unique values from a range, and you can wrap it in COUNTA to get the count:
=COUNTA(UNIQUE(A2:A20))
This formula returns the number of unique text and number entries. It ignores blanks automatically (COUNTA doesn’t count empty cells). For example, if A2:A20 has 15 entries with 3 duplicates, it returns 12. On Excel for the web, UNIQUE is also available in Microsoft 365.
If you want to count unique values that meet a condition (e.g., only “Yes” entries), you can combine UNIQUE with FILTER:
=COUNTA(UNIQUE(FILTER(A2:A20, B2:B20="Yes")))
This counts unique values in A2:A20 where the corresponding cell in B2:B20 is “Yes”.
Method 3: PivotTable for a Visual Count
A PivotTable is another way to see unique counts without formulas. Here’s how:
- Select your data (including headers) and go to Insert > PivotTable.
- In the PivotTable Fields pane, drag the field you want to count into both the Rows area and the Values area.
- Click the drop-down arrow next to the value field (e.g., “Count of Fruit”) and select Value Field Settings.
- Choose “Distinct Count” from the list. This option is available in Excel 2013 and later.
The PivotTable will show each unique item once, with a distinct count in the totals. This method is ideal if you also want to see the list of unique items, not just the count.
Common Problems and How to Fix Them
Several issues trip up users when trying to count unique values:
- #DIV/0! error: This happens with the SUMPRODUCT formula if the range contains blank cells. Fix by using the blank-safe version or removing blanks.
- Text with trailing spaces: “Apple” and “Apple ” are treated as different values. Use TRIM to clean data first:
=COUNTA(UNIQUE(TRIM(A2:A20)))(requires Excel 365). - Numbers stored as text: A value like 5 (number) and “5” (text) are counted separately. Convert text to numbers using the VALUE function or Text to Columns.
- Case sensitivity: COUNTIF is not case-sensitive, so “apple” and “APPLE” are the same. If you need case-sensitive unique counts, you’d need a more complex array formula or a helper column.
Comparing the Methods: Which Should You Use?
The table below summarizes the key differences:
- SUMPRODUCT/COUNTIF: Works in all Excel versions, but can be slow on large datasets (over 10,000 rows) and has the blank cell issue.
- UNIQUE/COUNTA: Fast and simple, but only available in Excel 365 and 2021. It also spills results if you use it alone, which some users find distracting.
- PivotTable: No formulas, great for interactive analysis, but it’s a manual step and not dynamic if your data changes unless you refresh.
For most users on Excel 365, =COUNTA(UNIQUE(range)) is the best choice. For older versions, the SUMPRODUCT formula is the standard workaround. If your data is large (over 50,000 rows), consider using a PivotTable or Power Pivot for better performance.
Frequently Asked Questions
Can I use COUNTIF to count duplicates only?
Yes, to count how many times a specific value appears, COUNTIF is exactly what you need. For example, =COUNTIF(A2:A20,"Apple") gives the total occurrences. To count all duplicate entries (i.e., total entries minus unique entries), use =COUNTA(A2:A20)-COUNTA(UNIQUE(A2:A20)) in Excel 365, or =COUNTA(A2:A20)-SUMPRODUCT(1/COUNTIF(A2:A20,A2:A20)) in older versions.
Why does my SUMPRODUCT formula return a decimal like 2.5?
That happens when your range contains blank cells or mixed data types. The division by COUNTIF can produce fractional values if a value appears an odd number of times and there are blanks. Use the blank-safe version or clean your data to get an integer.
Is there a way to ignore duplicates in COUNTIF without a helper column?
No, COUNTIF itself cannot ignore duplicates. You need to use a different function (like UNIQUE) or an array formula. A helper column is only needed if you want to count based on multiple criteria; otherwise, the SUMPRODUCT or UNIQUE methods work without one.
Does the UNIQUE function work in Excel for Mac?
Yes, UNIQUE is available in Excel for Microsoft 365 on Mac, and in Excel 2021 for Mac. The formula syntax is the same. On Mac, you can also use the same keyboard shortcuts, but the Command key replaces Ctrl in most cases.
How do I count unique values with multiple conditions?
In Excel 365, use =COUNTA(UNIQUE(FILTER(A2:A20, (B2:B20="Yes")*(C2:C20>10)))). In older versions, you’d need a more complex array formula like =SUMPRODUCT((B2:B20="Yes")*(C2:C20>10)/COUNTIFS(A2:A20,A2:A20,B2:B20,"Yes",C2:C20,">10")), but this is tricky and error-prone. If you need multiple conditions, consider using a PivotTable or upgrading to Excel 365.
Can I use COUNTIF to ignore duplicates in conditional formatting?
Yes, you can highlight duplicate values using conditional formatting, but that’s a different task. To highlight unique values, you can use a formula rule like =COUNTIF($A$2:$A$20,A2)=1 applied to the range. This will highlight cells that appear only once, effectively ignoring duplicates for the purpose of formatting.
Related
- String in Java: Meaning, Methods, and Examples
- Java String.join(): How It Works and When to Use It
- C++ String Count: How to Count Characters
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Power Query IF Statements: Examples and RulesIn Excel Power Query, an IF expression uses M language syntax: if condition then value_if_true else value_if_false. Add…
- Excel Formula for Calculating a Loan PaymentThe core Excel formula for calculating a loan payment is =PMT(rate, nper, pv), where rate is the periodic…
- Match Excel Formula for Two Columns: Compare ValuesTo compare two cells on the same row in Excel, enter =IF(A2=B2,"Match","Different") in C2 and fill it down.…
- Excel Formula for Days Left Till DateThe simplest Excel formula for days left until a date is =DATE(2025,12,31)-TODAY(), which returns the number of days…
- Excel MATCH Expressions: Syntax and ExamplesExcel’s MATCH function finds a value in a one-dimensional range and returns its position within that range—not the…
- Concatenate Names in Excel: Formulas and ExamplesTo concatenate names in Excel, join the cells with the ampersand operator and add a space between them:…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.