JOIN SHEET
Get Started

How to count how many distinct values appear more than once

Find how many different codes are duplicated in a list. Count each repeated code once, whether it appears twice or many times.

JOIN SHEETPublished Updated
A six-row code list contains A three times, B twice and C once.
Illustration with fictional data, not a screenshot of the app.

If A appears three times and B appears twice, how many different codes repeat? The answer is 2: A and B. In JOIN SHEET, count each code's occurrences, keep the codes that occur more than once, then count that shorter list.

Decide whether you need the number of rows or the number of codes

In this fictional six-row list, five rows contain a repeated code: three A rows and two B rows. If you set aside the first occurrence of each code, there are three extra copies. The number of different codes that repeat is two: A and B. This guide calculates that last count.

Code
A
A
A
B
B
C

Import these six rows with one code per row. The first summary will calculate the occurrence counts.

Summarize, filter, then count the groups

  1. Select the sheet and open Refine results. Add Summarize with Code under Group by.
  2. Keep the default Count method and enter Occurrences in Output field name.
  3. Add Filter below it. Select Occurrences, Greater than, and 1.
  4. Add another Summarize step. Leave Group by empty, keep Count, and enter Repeated codes in Output field name.
  5. Check the three-step order and choose Apply.

To inspect the intermediate counts, initially apply just the first summary. Check A=3, B=2 and C=1 in Result preview, then reopen Refine results and add the remaining steps.

Diagram of counting each code, keeping repeated codes and counting the remaining groups.

The final count is two repeated codes

The second summary counts the two rows left for A and B, so the final result is:

Repeated codes
2

If no code repeats, the final count is zero. If you want the list of repeated codes instead of one total, stop after the filter and leave out the last summary.

Diagram of the final Repeated codes value of 2.

Should blank codes count as a repeated value?

If an empty code should not count, add a Code Is not empty filter before the first summary. If you need to distinguish branches, group by both Code and Branch at the start; the rest of the steps stay the same.

Capitalization and surrounding spaces can create separate groups. Review the source values rather than assuming that visually similar codes are identical. This workflow counts only the data included after any existing filters.

Try it with your own files

Start with JOIN SHEET ↗

Use a desktop computer for Excel and CSV work.
Opens in a new tab without replacing your current work.

Example last verified:

Sources

Counting duplicates with each instance once — Reddit r/excel

The fictional A, B and C list illustrates the counting question above.

Related articles