Multiple IF/AND statements

Hello,

Trying to combine the following formulas based on changing criteria and not sure how to put them together. Each one works on it's own but as soon as I try to add another one I get an #UNPARSABLE error.

IF(AND([Vendor Assignment]@row <> "", [Window End]@row = ""), "Vendor Assigned", "")

IF(AND([Vendor Assignment]@row <> "", [Window End]@row <> ""), "Site Survey Scheduled", "")

IF(AND([Window End]@row < TODAY(), [Date Survey Received by Internal Team]@row = ""), "Late - Requested Update", "")...on a side note on this formula, want to figure the date as 3 days less than today.

Not sure if I need to add an OR somewhere. Any assistance would be greatly appreciated.

Sharon C

Best Answer

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Answer ✓

    Ok give this a try:

    =IF(AND([Vendor Assignment]@row <> "", ISBLANK([Window End]@row)), "Vendor Assigned", IF(AND([Vendor Assignment]@row <> "", [Window End]@row <= TODAY(-4)), "Site Survey Scheduled", IF(ISBLANK([Vendor Assignment]@row), " ", IF(AND(ISBLANK([Date Survey Received by Internal Team]@row), [Window End]@row < TODAY()), "Late Requested Update"))))

Answers

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    Try this:

    =IF(AND([Window End]@row <= TODAY(-3), ISBLANK([Date Survey Received by Internal Team]@row)), "Late Requested Update", IF(AND(ISBLANK([Window End]@row), NOT(ISBLANK([Vendor Assignment]@row))), "Vendor Assigned", IF(AND(ISBLANK([Vendor Assignment]@row), NOT(ISBLANK([Window End]@row))), "Site Survey Scheduled")))

  • Sharon Castiglia
    Sharon Castiglia ✭✭✭✭✭

    Hi Nic,

    Thank you for the quick answer. It did not return an unparseable which is AWESOME. However, the Late Requested Update statement is populating even if the Window End Date is blank. That statement should only populate if there is a date in the window end cell.

  • Sharon Castiglia
    Sharon Castiglia ✭✭✭✭✭

    I stand corrected on the above. The part of the formula:

    IF(AND(ISBLANK([Vendor Assignment]@row), NOT(ISBLANK([Window End]@row))), "Site Survey Scheduled"

    The Vendor Assignment should not be blank. I tried changing to the following:

    IF(AND, NOT(ISBLANK([Vendor Assignment]@row), NOT(ISBLANK([Window End]@row))), "Site Survey Scheduled"

    But got the Dreaded #UNPARSEABLE error.

    Where am I off

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    Now I am getting myself in a loop of errors. So I can straighten out the conditions - can you confirm the logic you are using:

    1) If Vendor Assignment cell is not empty, but Window End is blank = Vendor Assigned

    2) If Vendor Assignment cell is not empty, but Windows End is now also not empty = Site Survey Scheduled

    3) If Window End is less than today and Date Survey is blank = Late Requested Update

    Is this correct? Do we need to first check on Logic #3 to go there first before checking the other 2 conditions?

  • Sharon Castiglia
    Sharon Castiglia ✭✭✭✭✭

    I tried to move the 3rd logic to the first position and got an unparsable error.

    =IF(AND(Vendor Assignment]@row), NOT(ISBLANK([Window End]@row)), "Site Survey Scheduled"), IF(AND([Window End]@row < TODAY(), ISBLANK([Date Survey Received by Internal Team]@row), "Late Requested Update")), IF(AND(ISBLANK([Window End]@row), NOT(ISBLANK([Vendor Assignment]@row))), "Vendor Assigned"))

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Answer ✓

    Ok give this a try:

    =IF(AND([Vendor Assignment]@row <> "", ISBLANK([Window End]@row)), "Vendor Assigned", IF(AND([Vendor Assignment]@row <> "", [Window End]@row <= TODAY(-4)), "Site Survey Scheduled", IF(ISBLANK([Vendor Assignment]@row), " ", IF(AND(ISBLANK([Date Survey Received by Internal Team]@row), [Window End]@row < TODAY()), "Late Requested Update"))))

  • Sharon Castiglia
    Sharon Castiglia ✭✭✭✭✭

    You are awesome. Just changed  <= TODAY(-4) to TODAY(-1) and all parameters were met.

    I gave you a Vote Up👏

  • Sharon Castiglia
    Sharon Castiglia ✭✭✭✭✭

    So I have a new parameter:

    Survey Reviewed By is not blank and Photos and Reports is not blank then Survey Received. Getting an unparseable with the formula below.


    =IF(AND([Photos and Reports]@row <> "", NOT(ISBLANK([Survey Reviewed By]@row)), "Survey Received", IF(AND([Vendor Assignment]@row <> "", ISBLANK([Window End]@row)), "Vendor Assigned", IF(AND([Vendor Assignment]@row <> "", [Window End]@row >= TODAY(-3)), "Site Survey Scheduled", IF(ISBLANK([Vendor Assignment]@row), " ", IF(AND(ISBLANK([Date Survey Received by Internal Team]@row), [Window End]@row < TODAY()), "Late - Requested Update")))

  • Sharon Castiglia
    Sharon Castiglia ✭✭✭✭✭

    Never mind, got it figured out.

    My new formula,

    =IF(NOT(ISBLANK([Photos and Reports]@row)), "Survey Received", IF(AND([Vendor Assignment]@row <> "", ISBLANK([Window End]@row)), "Vendor Assigned", IF(AND([Vendor Assignment]@row <> "", [Window End]@row >= TODAY(-3)), "Site Survey Scheduled", IF(ISBLANK([Vendor Assignment]@row), " ", IF(AND(ISBLANK([Date Survey Received by Internal Team]@row), [Window End]@row < TODAY()), "Late - Requested Update")))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!