Excel: Counting duplicates
Published: 11 Jan 2023
There's not a button to count duplicates, but there's other ways to do it in Excel
Once in a while, one needs to count how many duplicates one have and there's way's to do this in Excel
[snippet]=COUNTIF($B$2:$B$34602,A3)[/snippet]
Above COUNTIF snippet, starts by defining what column (B in this case) and row (2 in this case), it starts on. Then it defines until where it will stop looking. Then A3 defines what value is should look for
| A | B | C | |
| 1 | Birthday | ||
| 2 | 2010 | 2010 | =COUNTIF($A$2:$A$20,B2) |
| 3 | 2001 | ||
| 4 | 2010 | ||
| 5 | ... |
Here we have told it to start looking on A2 and look until A20, for all that matches 2010
Subtract first occurrence
In some occurances, you only want to list how many duplicates there are and not include the first occurrence
[snippet]=COUNTIF($B$2:$B$34602,A3)-1[/snippet]
By adding -1 at the end, one will be subtracted from the total and you'll know how many duplicates you have
Tags: #Excel
We sometimes publish affiliate links and these always needs to follow our editorial policy, for more information check out our affiliate link policy
