Counting duplicates only

[Sekars]

Hello Friends

I need to count the duplicates only in light yellow cells.

In this sheet only 4 duplicates are there 0010-35GA5, 0010-DG73Z, 0242-D37ZZ, 0242-0B5BZ.

So in light yellow cell the formula should show as 4.

thanks in advance.

[JohnTopley]

Helper in B

=IF(AND(COUNTIF($A$2:$A$17,$A2)>1,COUNTIF($A$2:$A2,A2)=1),1,0)

in D2

=SUM(B2:B17)

[jewelsharma]

try the following array formula:

Please Login or Register  to view this content.

Note: Array Formula must be entered/confirmed using [Ctrl] + [Shift] + [Enter]

[Sekars]

Hello jewelsharma and Johntopley

Both of your formulas working well. Thanks for your formulas.

jewelsharma : My data will be changing everytime and everytime i need to change the $A$17 can we make the formula as $A$2:$A$17 to $A$2:$A$1000. Even if blank cells under A17 then the formula is working then it will be very fine.

Thanks again.

[jewelsharma]

In such a case, best to define a dynamic named range so the name itself remains the same, but extends to cover the added data; and then used the named range in the formula. Check this link.

Trust you'll mark this thread as SOLVED.
Cheers!

[Sekars]

Hello jewelsharma

My data will be changing everytime and everytime i need to change the $A$17 can we make the formula as $A$2:$A$17 to $A$2:$A$1000. Even if blank cells under A17 then the formula (needs to work) if working then it will be very fine.

Thanks again.

[Sekars]

Hello jewelsharma

I used the formula

=SUM(IF(FREQUENCY(MATCH($A$2:$A$1000&0,$A$2:$A$1000&0,0),MATCH($A$2:$A$1000&0,$A$2:$A$1000&0,0))>1,1))-1

and got solved.

thank you