formula ro indicate no. for dup ref where value for second ref has a zero

[Howardc1001]

i have references in Col B and values in Col C

Where the references are the same and the value for the duplicate reference (second ref) is zero, then 1 is to be assigned ,otherwise 0

I am able to determine which of the references are duplicates, but need to amend formula to assign 1 where duplicate reference i.e . the second appearance of the same reference in col b has a value of zero in same row as the second reference, otherwise assign 0


your assistance is most appreciated

[AliGW]

Is this what you want?

=IF(COUNTIF($B$2:$B$7,B2)>1,IF(C2=0,1,0),"")

[JohnTopley]

Try

in D2

=IF(AND(COUNTIF($B$2:$B2,$B2)>1,$C2=0),1,0)

[Howardc1001]

Thanks for the help Guys


The formula provided by Ali gives me the correct result

[AliGW]

No worries. You can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.