Search code examples
if-statementgoogle-sheetsarray-formulas

IF or IFS google sheet array formula

My array formula needs to be updated to include another if statement.

The original formula, works great: =ARRAYFORMULA(IF(((O2:O<TODAY())*(I2:I<>"Delivered"))>0,"🔴 Past due","⚫ On track")) Translates to: If task due date is less than today and the status is not equal to delivered, then past due, else on track.

I now need the formula to read: If task due date is less than today and the status is not delivered, then "past due" OR if task due date is blank and status is equal to not started, then leave the cell blank.

I am not sure if I should be using IF or IFS.

My attempt at re-writing the formula is below.

=ARRAYFORMULA(IFS(((O2:O<TODAY())(I2:I<>"Delivered"))>0,"🔴 Past due",[(O2:O=""())(I2:I<>"Not Started"))>0,""]))

Basically the task is either past due or the cell should be blank or if this is even possible.

spreadsheet image

Solution

  • IFS is not supported under arrayformula properly. use:

    =ARRAYFORMULA(
 IF((O2:O<TODAY())*(I2:I<>"Delivered"), "🔴 Past due", 
 IF((O2:O="")*(I2:I<>"Not Started"), "", )))

    enter image description here

    update

    =ARRAYFORMULA(
 IF((O2:O="")*(I2:I="Not Started"), "", 
 IF((O2:O<TODAY())*(I2:I<>"Delivered"), "🔴 Past due", )))