This is the spreadsheet I'm working on. I am trying to figure out a formula for conditional formatting so that it only highlights the cells in red if none of the cells from columns N to Y of the Failed tab says either "Yes, I take this medication within the specified dosage range" or "I am unsure If I take this".

  • What column should be highlighted in this case? All columns N-Y or something else?
    – user79865
    Jul 11, 2016 at 12:53
  • columns N-Y should be highlighted; none if the condition is met
    – Vee
    Jul 11, 2016 at 12:59

1 Answer 1


After selecting the range N2:Y, apply to it conditional formatting with "Custom formula is:"

=sum(arrayformula(n(regexmatch($N2:$Y2, "Yes, I take this medication within the specified dosage range|I am unsure If I take this")))) = 0

Explanation: $N2:$Y2 refers to the range N-Y in the current row. regexmatch($N2:$Y2, "this|that") returns True when the cell contains either "this" or "that" (this is a substring search, which is appropriate in your case). Then N converts True to 1 and False to 0. The whole array of these 1-0 values is then summed, and the formatting applies if the sum is 0, meaning there were no matches.

  • Thanks @Bookend. The formula works on non-empty cells but the empty cells are formatted in red because they met the criteria too. How do I get around that?
    – Vee
    Jul 12, 2016 at 3:53
  • 1
    You can exclude empty cells by changing the formula to =len(N2)*(sum(...) = 0) where sum(...) is as above
    – user79865
    Jul 12, 2016 at 4:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.