CODEWITHSUNDEEP
google-sheetsarray-formulas
Johnny
Johnny

Reputation: 41

How to use ArrayFormula in Google Sheets, with multiple IF conditions?

So I just found out about ArrayFormula and am trying to convert my spreadsheet to utilize it, to be more efficient. Its worked wonderfully on all my columns except one, which is giving me trouble.

Currently, I have this formula in it (and copied to each cell):

=IF(C2="Immediate",
  D2+1,
  IF(C2="3 Day",
    WORKDAY(D2,3,Holidays!$B$2:$B$11),
    IF(C2="5 Day",
      WORKDAY(D2,5,Holidays!$B$2:$B$11),
      IF(ISBLANK(C2),
        IFERROR(1/0)
      )
    )
  )
)

It works wonderfully to calculate a date, based on a dropdown menu I have in another column. I cant figure out how to convert this to utilize ArrayFormula, as I get an error as soon as I add a second IF statement.

I can get the first statement to work with this formula: 

=ARRAYFORMULA(IF(C2:C="Immediate",D2:D+1,))

But as soon as I try to add a second IF statement(such as with the formula below) I get an error.

How do I do this? I figure its probably simple but I cant figure it out!

Im trying to use a simpler formula to figure out where I am going wrong, and same thing will happen with the following formula so Im clearly doing something wrong! 

=ARRAYFORMULA(IF(A1:A=1,"Hello",),IF(A1:A=2,"Goodbye",))

With only the first IF statement, it works. As soon as I add the second, I get #N/A

Upvotes: 2

Views: 33989

Answers (5)

Ahmed Awad
Ahmed Awad

Reputation: 1

This is the best arrayformula with multi if conditions I usually use

AND Operator

=Arrayformula(if(isblank(A2:A),,if((condition1) * (condition2) * (condition3),statement, if((condition1) * (condition2) * (condition3), statement, ))))

OR Operator

=Arrayformula(if(isblank(A2:A),,if((condition1) + (condition2) + (condition3),statement, if((condition1) + (condition2) + (condition3), statement, ))))

Example :

=Arrayformula(if(isblank(A2:A),,IF(((F2:F="Completed") * (J2:J <= 50)), $AT$3, IF(((F2:F="Completed") * (J2:J > 50) * (J2:J < 150)), $AT$4, IF(((F2:F="Completed") * (J2:J>150) * (J2:J < 250)), $AT$5, IF(((F2:F="Not_loaded") * (J2:J = 0)), $AT$6, 0))))))

Kindly note that braces are required to work correctly.

Upvotes: 0

vstepaniuk
vstepaniuk

Reputation: 868

Your first formula, expanded to arrays, works perfectly well for me

=ARRAYFORMULA(IF(C2:C5="Immediate",
                 D2:D5+1,
                 IF(C2:C5="3 Day",
                    WORKDAY(D2:D5,3,Sheet2!$B$2:$B$11),
                    IF(C2:C5="5 Day",
                       WORKDAY(D2:D5,5,Sheet2!$B$2:$B$11),
                       IF(ISBLANK(C2:C5),
                          IFERROR(1/0)
                       )
                    )
                 )
              )
 )

Upvotes: 0

Matt Campbell
Matt Campbell

Reputation: 171

I know this is old but maybe try this formula:

={"Header_Cell_Name_Here";
  ArrayFormula(
    IFS(
      $C$2:$C="","",
      $C$2:$C="Immediate", $D$2:$D + 1,
      $C$2:$C="3 Day", WORKDAY($D$2:$D,3,Holidays!$B$2:$B$11),
      $C$2:$C="5 Day", WORKDAY($D$2:$D,5,Holidays!$B$2:$B$11)
    )
  )
}

IFS is a great alternative to IF. Rather than taking three arguments like you do with an IF statement:

IF(logical_expression, value_if_true, value_if_false)

an IFS statement can handle any number of conditions:

IFS(condition1, value1, [condition2, ...], [value2, ...])

Upvotes: 2

Spon4ik
Spon4ik

Reputation: 73

you can use that formula

=ArrayFormula(IF(C2:C="",IFERROR(1/0),IF(C2:C="Immediate",D2:D+1,IF(C2:C="3 Day",WORKDAY(D2:D,3,Holidays!$B$2:$B$11),IF(C2:C="5 Day",WORKDAY(D2:D,5,Holidays!$B$2:$B$11),IF(ISBLANK(C2:C),IFERROR(1/0)))))))

here is an example

Upvotes: 0

Chris Hick
Chris Hick

Reputation: 3094

Try this formula somewhere on row 1 of your sheet:

=ARRAYFORMULA(IF(A:A="",,IF(A:A=1,"Hello",IF(A:A=2,"Goodbye","Other Result"))))

Upvotes: 1

Related Questions