Counting Duplicates in 1 formula

[chefpolo]

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

[Eastw00d]

There are more ways, here a few:

Cheers
Erwin

[Fluff13]

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.

[Eastw00d]

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

[Fluff13]

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

[Eastw00d]

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

[FlameRetired]

Another way. Also Array entered.

Please Login or Register  to view this content.

[Fluff13]

@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.

[Eastw00d]

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