IF formula with multiple criteria

Natalia Kataoka

I am attempting to write a formula based on 2 separate criteria, though I feel like I need some type of "BUT" statement and could use some help.

I want a picklist column to read YES or NO depending on the following:

IF Ins Status = Current OR N/A AND Agreement EXE is checked THEN Approved for Work = YES, BUT, IF Agreement Type = Truck AND Ins Status = Current AND Agreement EXE is checked AND if AB5 Compliant is checked THEN Approved for Work = YES, otherwise NO.

In a nutshell, there is a third requirement if the type = truck, that does not apply to any other type.

I have the first portion working correctly, I am just unsure how to add the second portion. It currently reads:

=IF(AND(OR([Ins Status]@row = "Current", [Ins Status]@row = "N/A"), [Agreement EXE]@row = 1), "YES", "NO")

Basically, I want a separate criteria if the Agreement Type = Truck, but every other type would be based upon a different criteria. In the attached screenshot, I would want TESCON to read NO under Approved for Work.

Paul Newcome

This would be a good example of when to use a nested IF formula.

=IF(this is true, then do this, otherwise do this)

You would drop the next IF statement into the "otherwise do this" portion.

=IF(this is true, do this, IF(something else is true, do this, otherwise do something else))

Natalia Kataoka

I have been attempting to nest the IFs, but the formula is still not working. I have tried rearranging, but no luck. This is what I have come up with so far, and while there is no error, it is not changing the field that I want it to.

=IF(AND([Agreement Type]@row = "TRUCK", [Agreement EXE]@row = 1, [Ins Status]@row = "Current", [AB5 Compliant?]@row = 1), "YES", IF(AND(OR([Ins Status]@row = "Current", [Ins Status]@row = "N/A"), [Agreement EXE]@row = 1), "YES", "NO"))

I also tried the below, with and without listing all the other Agreement Types.

=IF(AND(OR([Ins Status]@row = "Current", [Ins Status]@row = "N/A", [Agreement Type]@row = "MSA", [Agreement Type]@row = "BPO", [Agreement Type]@row = "MPO"), [Agreement EXE]@row = 1), "YES", IF(AND([Agreement Type]@row = "TRUCK", [Agreement EXE]@row = 1, [Ins Status]@row = "Current", [AB5 Compliant?]@row = 1), "YES", "NO"))

Paul Newcome

Based on your original post where you stated:

I want a picklist column to read YES or NO depending on the following:

IF Ins Status = Current OR N/A AND Agreement EXE is checked THEN Approved for Work = YES, BUT, IF Agreement Type = Truck AND Ins Status = Current AND Agreement EXE is checked AND if AB5 Compliant is checked THEN Approved for Work = YES, otherwise NO.

Give this a try...

=IF(OR(AND(OR([INS Status]@row = "Current", [INS Status]@row = "N/A"), [Agreement EXE]@row = 1), 
AND([Agreement Type]@row = "Truck", [INS Status]@row = "Current", [Agreement EXE]@row = 1, 
[AB5 Compliant?]@row = 1)), "YES", "NO")

Natalia Kataoka

Hm, that one is still not working correctly, but no error.

Do I need to specify any of the other Agreement Types?

Paul Newcome

The above formula will cover the below based on how I read your original post (the quoted portion in my previous comment)...

YES will be generated by the following:

• [INS Status] = "Current" or "N/A"
• and [Agreement EXE] is checked

or

• [Agreement Type] = "Truck"
• and [INS Status] = "Current"
• and [Agreement EXE] is checked
• and [AB5 Compliant?] is checked

If a row does not meet one of those sets of requirements, it will generate a NO. If you have other combinations that could generate a YES then they would need specified.

Natalia Kataoka

Ah, ok got it. I do think I'll need to specify some other combinations. I will play with that, thanks for your help!

Natalia Kataoka

Figured it out!

=IF(OR(AND(OR([Ins Status]@row = "Current", [Ins Status]@row = "N/A"), OR([Agreement Type]@row = "BPO", [Agreement Type]@row = "MBPO", [Agreement Type]@row = "MSA"), [Agreement EXE]@row = 1), AND([Agreement Type]@row = "TRUCK", [Ins Status]@row = "Current", [Agreement EXE]@row = 1, [AB5 Compliant?]@row = 1)), "YES", "NO")

Paul Newcome

Excellent. Happy to help! 👍️