I have this formula (1) in Google sheets:
=IF($A2="","",IF(AND(($G2 <= EOMONTH(TODAY(),-5)),($G2 <> ""),OR($H2 > EOMONTH(TODAY(),-5),$H2 = ""),OR($E2 > EOMONTH(TODAY(),-5),$E2 = "")),1,0))
which I want to convert to an arrayformula. I tried this formula (2) but I get a "#N/A no match" error:
=ARRAYFORMULA (
IFs (
ROW(A:A) = 1, "Contract Published, " & TEXT(EDATE(TODAY(),-5),"mmm-YY"),
IF(AND((G:G <= EOMONTH(TODAY(),-5)),(G:G <> ""),OR(H:H > EOMONTH(TODAY(),-5),H:H = ""),OR(E:E > EOMONTH(TODAY(),-5),E:E = "")),1,0),)
)
I also tried this formula (3), but get a "Formula parse error"
=ARRAYFORMULA (
IF(ROW(A:A) = 1, "Contract Published, " & TEXT(EDATE(TODAY(),-5),"mmm-YY")),
IF((G:G <= EOMONTH(TODAY(),-5),G:G <> ""),
IF((H:H > EOMONTH(TODAY(),-5),H:H = ""),
IF((E:E > EOMONTH(TODAY(),-5),E:E = "")),1,0)))
Does anyone have an idea what Im doing wrong, or how I can convert formula (1) into an Arrayformula?
try in row 2:
=ARRAYFORMULA(IF(A2:A="",,
IF(((G2:G <= EOMONTH(TODAY(), -5))*
(G2:G <> "")*
((H2:H > EOMONTH(TODAY(), -5)) + (H2:H = ""))*
((E2:E > EOMONTH(TODAY(), -5)) + (E2:E = ""))), 1, 0)))