In this lesson, I’ll show you a few ways you can use to count duplicate values in Excel. Some of these formulas are more complicated than others.
We are going to use the following example.
Let’s make it more appealing visually go to HOME >> Styles >> Conditional Formatting >> Highlight Cells Rules and select Duplicate Values.
Count all duplicate values
The first formula is the most complicated one. It’ll count the number o duplicates and sum them, so we will get a single result.
Enter this formula, and press Ctrl + Shift + Enter to insert it as an array formula.
|
1 |
=ROWS($A$2:$A$10)-SUM(IF(COUNTIF($A$2:$A$10,$A$2:$A$10)=1,1,0)) |
Count duplicate instances
Now, instead of counting all duplicates at once, let’s separate them into different names, and count how many times the same name appears.
Enter the following formula into cell D2 and AutoFill them for other results.
|
1 |
=COUNTIF($A$2:$A$10,C2) |
Count duplicate instances without the first occurrence
It’s very similar to the last method. Just subtract 1 if the value is at least one. We don’t want to subtract from values that don’t appear inside the column because we would get negative values.
In this case, let’s use the IF function.
|
1 |
=IF(COUNTIF($A$2:$A$10,C2)>0,COUNTIF($A$2:$A$10,C2)-1,0) |
The second argument of the IF function will be executed if the result of the COUNTIF function is greater than 0, otherwise, it returns 0.
You can count all duplicates by summing the occurrences.
Count duplicates without using the function
There is a way to count the number of duplicates without using the complicated formula.
Select values you want to find duplicates and navigate to Data >> Sort & Filter >> Advanced. Select Copy to another location and check Unique records only.
After you click OK, you are going to have unique values next to your list. The difference is the number of duplicates.
