Excel: Counting duplicates

There's not a button to count duplicates, but there's other ways to do it in Excel
Excel: Counting duplicates

Once in a while, one needs to count how many duplicates one have and there's way's to do this in Excel

=COUNTIF($B$2:$B$34602,A3)

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

ABC
1Birthday
220102010=COUNTIF($A$2:$A$20,B2)
32001
42010
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

=COUNTIF($B$2:$B$34602,A3)-1

By adding -1 at the end, one will be subtracted from the total and you'll know how many duplicates you have

We sometimes publish affiliate links and these always needs to follow our editorial policy, for more information check out our affiliate link policy

You might also like

Waking up during the night to pee could mean you're peeing wrong
Health

Waking up during the night to pee could mean you're peeing wrong

Powder vs Liquid vs Capsul Detergent
Cleaning

Powder vs Liquid vs Capsul Detergent

Arme riddere (Norwegian Cinnamon Toast)
Breakfast

Arme riddere (Norwegian Cinnamon Toast)

Comments

Sign up or Login to post a comment

There are no comments, be the first to comment.