Excel COUNTIF Duplicate Values Only Once

Formulas & functions · Updated November 22, 2026

To count duplicate values only once in Excel, you need to count each distinct value that appears more than once, not each occurrence. The simplest way is to use =SUMPRODUCT((COUNTIF(range, range)>1)/COUNTIF(range, range)) for a range, or =SUMPRODUCT(--(COUNTIF(range, range)>1)) if you only need the number of distinct duplicated values (not their total occurrences). The first formula counts each duplicate value once (so if “apple” appears 3 times and “banana” appears 2 times, it returns 2). The second formula also returns 2 in that case, but it counts the number of distinct values that have duplicates, not the total occurrences of duplicates. This article explains both approaches, how they work, and when to use each.

Understanding the Problem: What “Count Duplicates Only Once” Means

When people ask to “count duplicates only once,” they usually mean one of two things:

  • Count the number of distinct values that appear more than once. For example, in the list {apple, apple, banana, banana, cherry}, the answer is 2 (apple and banana).
  • Count the total number of duplicate occurrences, but treat each value’s duplicates as a single unit. This is essentially the same as the first, because each duplicated value contributes 1 to the count.

It’s important to clarify because some users actually want to count the total extra occurrences (e.g., in the same list, that would be 3: one extra apple and two extra bananas). That’s a different formula: =SUMPRODUCT((COUNTIF(range, range)>1)*(COUNTIF(range, range)-1)).

How the COUNTIF Function Works in This Context

COUNTIF normally counts how many times a specific value appears. For example, =COUNTIF(A2:A10, "apple") returns the number of cells containing “apple”. To count duplicates, you need to compare each cell against the entire range. The trick is to use an array of criteria: COUNTIF(range, range) returns an array of counts, one for each cell. For instance, if A2:A10 contains apple three times, banana twice, and cherry once, then COUNTIF(A2:A10, A2:A10) returns {3;3;3;2;2;1} (assuming the order matches).

This array is the foundation for all duplicate-counting formulas. The challenge is to turn that array into a single number that counts each duplicated value only once.

Formula 1: Count Distinct Duplicate Values (Each Value Once)

Assume your data is in A2:A20. To count how many distinct values appear more than once, use:

=SUMPRODUCT(--(COUNTIF(A2:A20, A2:A20)>1))

How it works:

  • COUNTIF(A2:A20, A2:A20) returns an array like {3;3;3;2;2;1;…}.
  • COUNTIF(...)>1 returns an array of TRUE/FALSE, where TRUE indicates the value appears more than once.
  • The double unary -- converts TRUE to 1 and FALSE to 0.
  • SUMPRODUCT sums the 1s, giving the count of cells that belong to a duplicated value. But because each duplicated value appears in multiple cells, this counts each duplicate value as many times as it appears. Wait—that’s not what we want!

Actually, the formula above counts the number of cells that have a duplicate, not the number of distinct duplicated values. For example, with apple appearing 3 times, banana 2 times, cherry 1 time, the formula returns 5 (3+2) because it counts each cell that has a duplicate. That’s not “each value once.” To get each distinct duplicated value once, you need a different approach.

The correct formula for distinct duplicated values is:

=SUMPRODUCT((COUNTIF(A2:A20, A2:A20)>1)/COUNTIF(A2:A20, A2:A20))

Let’s test it: For apple (3 occurrences), each of its three cells gets a value of (TRUE)/3 = 1/3. Summing the three gives 1. For banana (2 occurrences), each gets 1/2, summing to 1. For cherry (1 occurrence), FALSE/1 = 0. Total = 2. Perfect.

So the formula =SUMPRODUCT((COUNTIF(range, range)>1)/COUNTIF(range, range)) is the one that counts each duplicated value exactly once.

Formula 2: Count Total Duplicate Occurrences (Extra Copies)

If you instead want to count how many extra copies exist (i.e., total occurrences minus the number of distinct values), use:

=SUMPRODUCT((COUNTIF(A2:A20, A2:A20)>1)*(COUNTIF(A2:A20, A2:A20)-1))

For the same example, apple contributes (3-1)=2, banana contributes (2-1)=1, total 3. This tells you how many rows you could delete to leave only unique values.

Using UNIQUE and COUNTA for a Simpler Approach (Excel 365/2021)

If you have Excel 365 or Excel 2021, you can use the UNIQUE function. To count distinct duplicated values:

  1. In a helper column, enter =UNIQUE(A2:A20) to list all distinct values.
  2. In another cell, use =SUMPRODUCT(--(COUNTIF(A2:A20, UNIQUE(A2:A20))>1)) to count how many of those distinct values appear more than once.

Alternatively, you can use a single formula with LET (Microsoft 365):

=LET(u, UNIQUE(A2:A20), SUMPRODUCT(--(COUNTIF(A2:A20, u)>1)))

This is more readable and avoids the division trick. However, the SUMPRODUCT method works in all Excel versions, which is why it’s the classic solution.

How to Choose the Right Formula

Ask yourself what you need the number for:

  • If you want to know “how many different items are duplicated” (e.g., how many products have more than one order), use the SUMPRODUCT((COUNTIF>1)/COUNTIF) formula.
  • If you want to know “how many extra rows exist beyond the first occurrence of each duplicated item” (e.g., to clean data), use the SUMPRODUCT((COUNTIF>1)*(COUNTIF-1)) formula.
  • If you want to count unique values overall (including singles), use =SUMPRODUCT(1/COUNTIF(range, range)) or =COUNTA(UNIQUE(range)).

Always test with a small sample to confirm which result matches your intent.

Common Problems and Pitfalls

  • Blank cells: COUNTIF counts blanks as a value. If your range includes blank cells, they will be counted as a duplicate if there are multiple blanks. To exclude blanks, use =SUMPRODUCT((A2:A20<>"")*(COUNTIF(A2:A20, A2:A20)>1)/COUNTIF(A2:A20, A2:A20)).
  • Text with leading/trailing spaces: “apple” and “apple ” are different. Use TRIM to clean data first.
  • Case sensitivity: COUNTIF is not case-sensitive, so “Apple” and “apple” are treated as the same. If you need case-sensitive, use SUMPRODUCT with EXACT, which is more complex.
  • Whole column references: Using A:A can slow down the formula because it processes millions of cells. Limit the range to your actual data.

Frequently Asked Questions

How do I count duplicates only once in Google Sheets?

Google Sheets has the same SUMPRODUCT formulas. The UNIQUE function also exists. For example, =SUMPRODUCT((COUNTIF(A2:A20, A2:A20)>1)/COUNTIF(A2:A20, A2:A20)) works identically. The only difference is that Google Sheets may handle array formulas differently, but SUMPRODUCT works without special entry.

Can I use COUNTIF with a condition to count duplicates only once?

Yes, you can add a condition by multiplying with a criteria range. For example, to count distinct duplicated values only for rows where column B equals “Yes”, use =SUMPRODUCT((B2:B20="Yes")*(COUNTIF(A2:A20, A2:A20)>1)/COUNTIF(A2:A20, A2:A20)). This works because the division still gives each duplicated value a total of 1 across its occurrences, but only for rows meeting the condition.

Why does my formula return a #DIV/0! error?

This error occurs if a value appears only once, because COUNTIF returns 1 and you divide by it—that’s fine. But if a cell is blank, COUNTIF returns 0 for that cell, causing division by zero. The formula (COUNTIF>1)/COUNTIF divides by zero for blanks. Fix by excluding blanks as shown above, or use IFERROR, but excluding blanks is cleaner.

Is there a way to count duplicates only once without a formula?

Yes, you can use a PivotTable: drag the field to Rows and to Values (as Count). Then in the pivot, filter the Count column to show only values greater than 1. The number of rows in the filter equals the distinct duplicated count. This is manual but useful for a one-off analysis.

Does the formula work with numbers and text mixed?

Yes, COUNTIF treats numbers and text as distinct types. For example, 123 and “123” are different. If you want them treated as the same, you’d need to convert one type first.

Alternative Approaches and Performance Considerations

For large datasets (tens of thousands of rows), the SUMPRODUCT formulas can be slow because they perform multiple full-range calculations. In Excel 365, the UNIQUE function is faster and more efficient. For older versions, consider using a helper column with =COUNTIF(A$2:A2, A2) to mark first occurrences, then use COUNTIFS to count only those marked as first and with a count greater than 1. For example, in B2: =COUNTIF(A$2:A2, A2), then drag down. Then use =SUMPRODUCT((B2:B20=1)*(COUNTIF(A2:A20, A2:A20)>1)). This is more efficient because it avoids the division trick.

Another alternative is to use Advanced Filter to extract unique values, then count how many of those appear more than once with a simple COUNTIF. This is manual but works in any version.

Remember to always save a copy of your workbook before running any macro or complex operation, though these formulas are non-destructive.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.