Google Sheets - Conditional formatting based on multiple cells in a row

Question

I'm trying to make conditional formatting in Google Sheets. Idea is to change colour (better to change also a value) of cells in J2:J1000 depending on other cells in a row. So, e.g. J2 should change its value (or just a colour) to 'Ok' (green) when values in E2:I2 are 'Ok' OR 'Not Relevant'

I'm trying something like =OR(($E:$I='Ok'),($E:$I='Not Relevant')) But all my attempts are incorrect.

Answer 1

paste this in J2 cell:

=ARRAYFORMULA(IF(((E2:E="ok")+(E2:E="not relevant"))*
                 ((F2:F="ok")+(F2:F="not relevant"))*
                 ((G2:G="ok")+(G2:G="not relevant"))*
                 ((H2:H="ok")+(H2:H="not relevant"))*
                 ((I2:I="ok")+(I2:I="not relevant")); "Ok"; ))

and then paste this in conditional formatting as a custom formula:

=ARRAYFORMULA(IF(((E2:E="ok")+(E2:E="not relevant"))*
                 ((F2:F="ok")+(F2:F="not relevant"))*
                 ((G2:G="ok")+(G2:G="not relevant"))*
                 ((H2:H="ok")+(H2:H="not relevant"))*
                 ((I2:I="ok")+(I2:I="not relevant")), 1, ))

demo spreadsheet

---

if the conditional formatting is still not working try:

=ARRAYFORMULA(IF(((E2:E="ok")+(E2:E="not relevant"))*
                 ((F2:F="ok")+(F2:F="not relevant"))*
                 ((G2:G="ok")+(G2:G="not relevant"))*
                 ((H2:H="ok")+(H2:H="not relevant"))*
                 ((I2:I="ok")+(I2:I="not relevant")); 1; ))

Answer 2

If you need all of the cells to be either OK or Not relevant, you need

=countif(E2:I2,"OK")+countif(E2:I2,"Not relevant")=columns(E2:I2)

If you need any of the cells to be OK or Not relevant, you need just the first part

=countif(E2:I2,"OK")+countif(E2:I2,"Not relevant")

Answer 3

Enter this Conditional Formatting Custom Formula in J2

=and(OR($E2="Ok",$E2="Not Relevant"),OR($F2="Ok",$F2="Not Relevant"),OR($G2="Ok",$G2="Not Relevant"),OR($H2="Ok",$H2="Not Relevant"),OR($I2="Ok",$I2="Not Relevant"))

Apply to J2:J1000

Answer 4

=IFERROR(ARRAYFORMULA(IFS(
  E2:E="OK","ok",E2:E="not relevant","ok",
  F2:F="OK","ok",F2:F="not relevant","ok",
  G2:G="OK","ok",H2:H="not relevant","ok",
  H2:H="OK","ok",H2:H="not relevant","ok",
  I2:I="OK","ok",I2:I="not relevant","ok"
)))

Answer 5

Try this

=OR(AND(ARRAYFORMULA(--(E2:I2={"Ok"}))), ARRAYFORMULA(--(E2:I2={"Not Relevant"})))

It will take the values from E2:I2, if they are either "Ok" or "Not Relevant" the result will be TRUE