Count Unique Values Among Duplicates in Excel

Formulas & functions · Updated November 22, 2026

To count distinct nonblank values that appear at least twice in Excel, with the values in A2:A100, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,(A2:A100<>"")*(COUNTIF(A2:A100,A2:A100)>1)))),0). It returns one count for each repeated value, not one count for every repeated row.

What “unique among duplicates” means

The phrase can describe two different calculations. One counts how many different values are repeated; the other counts how many rows belong to repeated groups. The formula above answers the first question. If “Pear” appears three times and “Apple” appears twice, it counts two unique repeated values, even though those groups contain five rows altogether.

This distinction matters in tasks such as finding repeated customer names, products, invoice IDs, or survey responses. Suppose a list contains the values A, A, A, B, C, C. There are two distinct values that occur more than once: A and C. There are four rows containing those repeated values. Neither answer is the total number of entries in the list, and neither is simply the number of extra copies after the first occurrence.

In Excel, “unique” here means one instance of each value in the result. It does not mean a value that appears only once. If you want to count values that occur exactly once, that is a different calculation.

The Microsoft 365 formula

With the source values in A2:A100, enter this in an empty cell:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A100,(A2:A100<>"")*(COUNTIF(A2:A100,A2:A100)>1)))),0)

The formula uses FILTER, UNIQUE, and dynamic arrays, which are available in Microsoft 365 and Excel 2021 or later. It also works in current Excel for Mac and Excel for the web. If your regional settings use semicolons to separate formula arguments, Excel may require semicolons instead of commas.

Use the actual bounds of your list in place of A2:A100. Avoid including a header in the range: a heading such as “Product” could be counted as a value if it occurs more than once elsewhere. The result is a single number in the formula cell. When there are no repeated nonblank values, IFERROR displays 0 rather than a no-results error.

How the formula gets the answer

The formula first checks each item in the range with COUNTIF. For each value, COUNTIF returns how many times that value occurs in the full range. The test >1 marks values that appear at least twice. The separate test A2:A100<>”” excludes blank cells, including cells whose formulas return an empty string.

FILTER keeps only entries that pass both tests. That filtered result may still contain several copies of a repeated value, so UNIQUE reduces it to one instance of each. ROWS counts the remaining entries. For a vertical list, ROWS is a straightforward way to turn the distinct results into a number.

IFERROR handles the case where FILTER has nothing to return. It also catches other errors, however, so a result of 0 is not proof that the source data is error-free. If you suspect cells contain errors, inspect the range before relying on the count.

Display the repeated values, not just the count

A number can confirm that duplicates exist, but seeing which values are repeated is often more useful. With the same values in A2:A100, enter this in a clear cell outside the source list:

=IFERROR(UNIQUE(FILTER(A2:A100,(A2:A100<>"")*(COUNTIF(A2:A100,A2:A100)>1))),"No repeated values")

The formula spills its results into cells below the formula cell. Leave that area empty so the results have room to appear; otherwise, Excel may show a #SPILL! error. The optional message appears when no nonblank values meet the condition. If you later add data, make sure the source range includes the new rows, or convert the list to an Excel table so its range can expand as rows are added.

Check the result with a small example

Imagine A2:A10 contains Apple, Pear, Apple, Plum, Pear, Pear, Kiwi, Kiwi, and a blank. Apple occurs twice, Pear three times, and Kiwi twice. Plum occurs once, and the blank is excluded. The formula returns 3 because the repeated-value groups are Apple, Pear, and Kiwi.

The spilled-list version returns those three labels once each. It does not return Pear three times, because UNIQUE removes repeated instances from the filtered output. If you instead need the number of rows belonging to repeated groups, that example’s answer is 7: two Apple rows, three Pear rows, and two Kiwi rows. State which measure you need before comparing your result with someone else’s; both totals can be correct for different questions.

Options for older Excel versions

Excel 2019 and earlier do not include the dynamic-array functions used in the formula. A helper column is a practical option for identifying repeated rows. With the list in A2:A100, put this formula beside the first entry, in B2:

=COUNTIF($A$2:$A$100,A2)

Fill B2 down through B100. Each helper cell shows the number of times its corresponding value appears in the full list. Filter column B to show values greater than 1, then copy the matching entries from column A to a separate area. On that copied list, use Data > Remove Duplicates. Count the remaining labels; the result is the number of distinct repeated values. Work on a copy, because Remove Duplicates deletes repeated entries from the selected range.

Another option is a PivotTable. Put the value field in Rows and also in Values, make sure the Values field summarizes by Count, and identify row labels whose count exceeds 1. The number of qualifying labels is the distinct repeated-value count. A PivotTable is useful when you also want each value’s frequency, but it may require refreshing after the source data changes. Menu labels and dialog details can vary slightly between Excel releases.

Common data issues that change the result

  • Extra spaces: “Pear” and “Pear ” may look alike but are not necessarily the same text value. Clean inconsistent data before counting; for example, use a separate cleanup column with TRIM if leading, trailing, or repeated ordinary spaces are the problem.
  • Case: Excel’s usual duplicate checks are not case-sensitive, so Apple and APPLE are generally treated as the same text for this purpose. If capitalization distinguishes records in your system, this formula does not provide a case-sensitive count.
  • Wildcards: COUNTIF treats asterisks and question marks in criteria as wildcard characters. Text values containing those characters can therefore behave unexpectedly. If such characters are meaningful in your data, test the result carefully and consider a method designed for exact matching.
  • Numbers stored as text: A numeric value and a text representation of that value can be treated differently by Excel operations. Imported IDs are a common source of this mismatch. Decide whether the two forms should count as one value, then standardize the column accordingly.
  • Dates with times: Cells formatted to show only a date may still contain different time portions. Two entries can look identical on screen but be distinct underlying date-time values. Remove or normalize times if the date alone should define a match.
  • Blanks and formulas: Truly blank cells and formulas returning an empty string are excluded by the blank test. Cells containing spaces are not blank, so they can count as values unless cleaned.

Cell formatting alone usually does not determine whether two values match. A number displayed with different decimal places, for example, can still have the same underlying value, while two dates that look the same may hide different time values. When the answer seems surprising, inspect the actual contents and data types rather than relying only on what the cells display.

Choose a method that fits the worksheet

For a live count that updates as values change, the dynamic-array formula is usually the most direct method in Excel 2021 or Microsoft 365. Use the spilled-list version when you need to review, copy, or report the repeated labels. For a one-time check in an older version, a helper column and a copied list provide a transparent route. Choose a PivotTable when frequencies are useful alongside the answer.

For large sheets, use a realistic bounded range or an Excel table rather than repeatedly calculating across entire columns. COUNTIF is evaluated for many values in the source range, so an unnecessarily large range can slow recalculation. A table also helps avoid forgetting new rows when data is appended, while a fixed range is simpler for a short, stable list.

Frequently Asked Questions

Does this count the number of duplicate rows?
No. It counts each repeated value once. To count rows belonging to repeated groups, filter or otherwise select the rows whose occurrence count exceeds one, then count those rows.

Does a value have to appear exactly twice?
No. The condition is greater than 1, so a value appearing two, three, or many times qualifies once in the distinct count.

Does the formula count blanks?
No. The A2:A100<>”” condition excludes empty cells and formulas that return an empty string. Cells containing spaces are not empty and should be cleaned if they should be ignored.

Why does my result differ from Remove Duplicates?
Check whether you are comparing the same question and range. Remove Duplicates produces a list with repeated entries removed; it does not by itself tell you which values occurred more than once. Also check spaces, data types, hidden time portions in dates, and case assumptions.

Can I use the formula to count values that appear only once?
Not as written. It specifically filters for occurrence counts greater than one. A once-only count needs the opposite condition: an occurrence count equal to one.

Will the formula include rows I add later?
Only if the referenced range expands. Replace the fixed range with a suitable larger range, or use an Excel table and structured references so added rows are included more naturally.

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.