google-sheetsgoogle-sheets-formulaconditional-formatting

Why is this conditional formatting rule not working in Google Sheets?

I need to make sure that if any row below 3 is checked and it contains any value in column A, then highlight cell I2.

The rule seems right, but it only works when checking I4 and not the ones below.

Formulas tried: =$I4 * LEN($A4) =AND($I4=TRUE, $A4<>"")

Here is a sample file, in case you want to give it a shot:

file

Solution

  • Your rule only tests row 4 so it makes sense it would ignore the other rows. If the rule needs to check Columns A and I from Row 4 to the end, then that needs to be in the rule.

    Use the following:

    =SUMPRODUCT(I4:I*LEN(A4:A))

    If you might also have numbers in Column I and don't want them interpreted as checked boxes, then use:

    =SUMPRODUCT((I4:I=true)*LEN(A4:A))