Google-sheets – How to add arrayformula function to this formula with IF function

google sheetsgoogle-sheets-arrayformula

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

https://docs.google.com/spreadsheets/d/17mmduo0em4PK0FDPxIRTX36CqhW6woI4jdyPSJaiGAM/edit#gid=1414263054

Best Answer

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:

=arrayformula( 
  ((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.

Related Topic