Error in formula for two IFs

[Daniel]

Hi everyone,

I have problem with the following formula, basically i am
testing the contents of two fields throughout the file,
where there is an occurance of a record with duplicate
values in the two fields I need to flag as a duplicate.

=IF(COUNTIF($B2:B$2000,B2)>1 AND IF(COUNTIF
($K2:K$2000,K2)>1,"Duplicate",""))

Anybody got any ideas?

P.S Thanks everyone for yoour help previouslt, it's been
invaluable!

Cheers

Daniel

 

[Frank Kabel]

Hi
use:
=IF(AND(COUNTIF($B2:B$2000,B2)>1,COUNTIF
($K2:K$2000,K2)>1),"Duplicate",""))

But I would think you probably meant:
=IF(SUMPRODUCT(--($B2:B$2000=B2),--($K2:K$2000=K2)),"Duplicate","")´
if both values have to be duplicated in the SAME row