# Conditionally formatting duplicate values in Google sheets with exclusion criteria?

I'm currently working in a Google sheet where I use the following formula to catch duplicate values with conditional formatting:

`=countif(\$B:\$B,B2)>1`

While this works on its own, I find that it does not account for duplicate values I do not want to be counted. In column O, I have values marking the row as "Canceled". So I want to have Google sheets only conditionally format cells if the following criteria are met:

• The value in column B has a duplicate

AND

• Neither duplicate value has the word "canceled" in column O for that row.

This is the formula I tried, but it no longer formats duplicates:

`=AND((countif(\$B:\$B,B2)>1=TRUE),ISNUMBER(SEARCH("Cancelled",\$O:\$O)=FALSE))`

I think what this has done has told the logic to not format duplicates unless there are no instances of "canceled" in column O.

• @user0 It's generally okay to leave UK spellings ("cancelled") intact when editing. Not a big deal, but just something to be mindful of. – jonsca Jul 18 '18 at 0:07

Solved my own question, in a bit of a brute force method. Since Google sheets was a bit iffy about letting me do an `AND` statement on `ISNUMBER(SEARCH())`, I added 3 helper columns which I hid:

• Column P `=ISNUMBER(SEARCH("Cancelled",\$O2))` *Checking for Cancelled on in a string TRUE/FALSE
• Column Q `=IF(\$P2=TRUE,1,0)` *Returns 1 in any column with TRUE for Cancelled
• Column R `=\$Q2+\$B2` *Adds a 1 to any order number where the previous formula returns a 1

I then used `=countif(\$R:\$R,R2)>1` and applied it to column B.

I believe the following formula, entered into the Conditional Formatting dialog as "Custom formula is" would do the trick alone (i.e., no need for hidden helper columns):

`=AND(O2<>"Cancelled",COUNTIF(B:B,B2)>1,COUNTIF(B:B,B2)>2*COUNTIFS(B:B,B2,O:O,"Cancelled"))`

One note: This formula will, in effect, cancel itself if there is a one-to-one correlation between one duplicate that is not canceled and one duplicate that is. However, in the case that there were multiple non-cancelled duplicates (say, three non-cancelled and one cancelled), then there is not a one-to-one match and all non-cancelled duplicates will be highlighted (since, without further information here, there is no way for Sheets to know which is the match.

If you only ever have a max of two duplicates in Column B, this formula should work fine.