Return to Question

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 counted. In column O, I have values marking the row as "Cancelled". 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 "cancelled" 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 "cancelled" in column O.

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.