Search code examples
if-statementgoogle-sheetsgoogle-sheets-formulaarray-formulasnested-if

Nested if functions in arrayformula


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?


Solution

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