I made a table that has two columns (namd & State). The 4th column(check) makes conditional formatting possible for any of these combinations: (if name is either "Ali" or "John", AND state is "FL"), a conditional formatting rule is applied only if the cell value of E2 is "NO".

I had to repeat this formula for all rows and I failed to wrap it in an arrayformula function.

I tried this but failed: =if(OR(AND(C2="John",D2="FL",$E$1="No"),AND(C2="Ali",D2="FL",$E$1="No")),1,0)

adding arrayformula: =ArrayFormula(if(OR(AND(C2:C="John",C2:D="FL",$E$1="No"),AND(C2:C="Ali",D2:D="FL",$E$1="No")),1,0))



1 Answer 1


Aggregating functions such as and() and or() do not give row-by-row results in an arrayformula() wrapper, but they can be replaced with Boolean arithmetic like this:

  ((C2:C = "John") + (C2:C = "Ali"))
  ((D2:D = "FL") * (E1 = "No"))

Each equals test will return a true or false, and these values get coersed to 1 and 0 when arithmetic operators are used.

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.