Finding duplicate value based on multiple criteria from another column

Copper Contributor

Hello fellow Excel users, 

I was hoping I would be able to find some support here with a formula I am hoping to find that solves my issue. 

I want to find duplicates in one column (A) based on the criteria (CA or US, NOT UK) in another column (B) and I want the result to show in column C. See example below:

 

Excelfresh_0-1618447883357.png

The issue is, I don't want to see a duplicate if that duplicate is only present within US (ex. row 3 & 4) and rather only if the duplicate is found both in US and CA. I also don't want to a duplicate to pop up if the value in column B is UK (ex. row 7 serial number is a duplicate, but the country sale is UK and NOT CA or US). 

Any help would be appreciated. 

1 Reply

@Excelfresh 

 

How about this?

=IF(AND(COUNTIFS($A$2:$A$7,A2,$B$2:$B$7,{"CA","US"}),OR(B2="CA",B2="US")),"Y","N")

 

If you place the formula in D2 and copy it down, it will produce an output like below...

Duplicate.jpg