Search code examples
google-sheetsgs-conditional-formatting

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

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.

Solution

  • 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, ))

    0

    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; ))