# Counting Duplicates in 1 formula

1. ## Counting Duplicates in 1 formula

I need a formula that counts duplicates in a specific column and returns the number of duplicates. For instance: I need the formula to go through column be and tell me that there are 2 values that are duplicated. Does not matter how many times they are duplicated, just need it to return the value of 2.

Column B:
12312
12318
12392
19232
12312
16782
12312
12318

2. ## Re: Counting Duplicates in 1 formula

There are more ways, here a few:

Cheers
Erwin

3. ## Re: Counting Duplicates in 1 formula

Assuming values in A2:A9 how about
=SUM((COUNTIF(A2:A9,A2:A9)>1)-(COUNTIF(A2:A9,A2:A9)=1))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

4. ## Re: Counting Duplicates in 1 formula

or in 1 formula:
``Please Login or Register  to view this content.``
gives the max amount of occurrences, the IF function reduces it to 2

Cheers
Erwin

5. ## Re: Counting Duplicates in 1 formula

Ignore the formula in post#3 it doesn't work

6. ## Re: Counting Duplicates in 1 formula

This one is doing too the job, but if the array has only unique numbers, the COUNTIF returns 0 (Anyway, you were not interested in that:
``Please Login or Register  to view this content.``
Personally, I would go for the formula in Post #4 , that is more accurate.

Cheers
Erwin

7. ## Re: Counting Duplicates in 1 formula

Another way. Also Array entered.
Formula:
`Please Login or Register  to view this content.`

8. ## Re: Counting Duplicates in 1 formula

@Eastw00d
I think the OP wants to now how many distinct values are duplicated, the number of times they occur are irrelevant.
You're formulae in posts 4 & 6 just return either 2 or 0, regardless of the data.

9. ## Re: Counting Duplicates in 1 formula

Maybe you are right; I read this, but I am not native English:
Does not matter how many times they are duplicated, just need it to return the value of 2.
For me I can see this only logical

Lets wait what the OP is telling....

Cheers
Erwin

There are currently 1 users browsing this thread. (0 members and 1 guests)