I'm collaborating on a Google Sheet with approximately 500 rows. I think potentially could use IF, ISBLANK and conditional formatting but I can't conceptualize a formula for this and I've tried reading examples. This Google Sheet has unique rows, but I cannot change the text which is why would like to conditionally format.

  1. J cell should be highlighted if text is "Not Met" AND column M is blank.
  2. K cell should be highlighted if text is "Not Met" AND column N is blank.
  3. I is the ultimately important column and should be highlighted if (J is "Not Met" AND M is blank) AND if (K is "Not Met" and N is blank). For example, the last row in the photo does not need to be highlighted because J is "Not Met" but is fulfilled by data in M AND K includes "Met".
  4. Additionally, if L cell has any data then I should not be highlighted (overrides J,K,M,N data).

I'm unable to print in color, so don't want to highlight green for "Met" or if J thru N fulfill the requirements, it should remain as is.

Here's ideally what I want it to look like: example of conditional formatting based on values

Is this possible?

  • 1
    To start, the third row of Column I in your posted image is highlighted; yet the conditions you describe are not met [i.e., "should be highlighted if (J is "Not Met" AND M is blank) AND if (K is "Not Met" and N is blank)]. In addition, EXPLAINING how to implement such rules would take longer than simple implementing them. Keeping in mind that this site is tended 100% by volunteer contributors and not paid employees, the best way to invite help is to share a link to your sheet (or a copy of it) with the link permissions set (when creating the link) to "Anyone with the link..." and "Editor."
    – Erik Tyler
    Oct 9 at 2:49
  • You can use the Contact Us form to have your accounts merged, so that you can freely edit your post.
    – Glorfindel
    Oct 9 at 7:36

The answer comprises a series of custom formula:

Column J =AND(J2="Not Met",isblank(M2))

  • Highlight if Column J = "Not Met" AND Column M is blank

Column K =AND(K2="Not Met",isblank(N2))

  • Highlight if Column K = "Not Met" AND Column N is blank

Column I

=or(not(isblank(L2)),AND(AND(J2="Not Met",isblank(M2)),AND(K2="Not Met",isblank(N2))))

  • This is in two parts driven by OR
    • Part#1: Highlight if Column L is not blank

    • not(isblank(L2))

    • Part#2: Combination of the Column J and K rules with AND applied

    • AND(AND(J2="Not Met",isblank(M2)),AND(K2="Not Met",isblank(N2)))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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