Multiple IF/AND statements

Sharon Castiglia

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

Nic Larsen

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

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

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

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

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

Correct.

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

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

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

I gave you a Vote Up👏

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

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