count duplicates

[mwperkins]

Hi
is there an array formula that can count how many duplicates exist in column A?

thanks
Mark

 

[jorismoerings]

Hi mark,

try this =SUM(1/COUNTIF($A$1:$A$100,$A$1:$A$100)) as array.
Adjust the range to your need and be aware this will only work if there are no blanks in your range.

An non array alternative could be: =SUMPRODUCT(($A$1:$A$100<>"")/COUNTIF($A$1:$A$100,$A$1:$A$100&""))

Hope this helps

 

[mwperkins]

many thanks, but both formulas tell me how many different values there are, not how many values occur more than once

 

[jorismoerings]

Hi Mark,

You're right. So add a simple count to the formula.
So it becomes: =COUNT($A$1:$A$100)-SUMPRODUCT(($A$1:$A$100<>"")/COUNTIF($A$1:$A$100,$A$1:$A$100&""))
This will give you the number of duplicate value in the list however not which one.

 

[mwperkins]

thank you