Multiple criteria for If formula

MattGS

Greetings,

Long time reader, first time asking.

I'm trying to build a column formula that will return a true or false value based on multiple criteria. The logic is:

If [Type1] is ill OR injured, AND [affiliation1] is staff, AND [while working1] is true, OR [Type2] is ill OR injured, AND [affiliation2] is staff, AND [while working2] is true THEN [workers comp] is true)

What I've come up with is:

=IF(AND([IP1 Type]@row = "Ill" OR "Injured", IF(AND([IP1 GS Affiliation]@row = "Staff", IF(AND([Did Incident Occur while IP1 was working?]@row =True, OR IF(AND([IP1 Type]@row = "Ill" OR "Injured", IF(AND([IP1 GS Affiliation]@row = "Staff", IF(AND([Did Incident Occur while IP1 was working?]@row =True

I don't know how to write the OR between segments nor the THEN for the action that changes the column to True.

Thanks!

Matt

Eric Law

Hello @MattGS

It should be =IF(OR(Type1, Type2), True, False)

Type1 should be IF(AND(OR([IP1 Type]@row = "Ill", [IP1 Type]@row = "Injured"), [IP1 GS Affiliation]@row = "Staff", [Did Incident Occur while IP1 was working?]@row =True), True, False)

Type2 should be the same but with 2. Together it should be

=IF(OR(IF(AND(OR([IP1 Type]@row = "Ill", [IP1 Type]@row = "Injured"), [IP1 GS Affiliation]@row = "Staff", [Did Incident Occur while IP1 was working?]@row =True), True, False), IF(AND(OR([IP2 Type]@row = "Ill", [IP2 Type]@row = "Injured"), [IP2 GS Affiliation]@row = "Staff", [Did Incident Occur while IP2 was working?]@row =True), True, False)), True, False)

Hope that helps!

Paul Newcome

You may need to adjust column names to match what you are using, but this syntax should get the job done:

IF(OR(AND(OR([IP1 Type]@row = "Ill", [IP1 Type]@row = "Injured"), [IP1 GS Affiliation]@row = "Staff", [Did Incident Occur while IP1 was working?]@row = 1), AND(OR([IP2 Type]@row = "Ill", [IP2 Type]@row = "Injured"), [IP2 GS Affiliation]@row = "Staff", [Did Incident Occur while IP2 was working?]@row = 1)), 1)

MattGS

Thank you both for your help with this. I used the first one I saw (@Paul Newcome ) and it worked great. I had minimized the number of Involved people (IP1, IP2), thinking I could duplicate what was suggested a few more times. I have four potential IPs. I tried this and am getting a syntax error with:

=IF(OR(AND(OR(AND(OR[IP1 Type]@row = "Ill", [IP1 Type]@row = "Injured"), [IP1 GS Affiliation]@row = "Staff", [Did Incident Occur while IP1 was working?]@row = "Yes"), AND(OR([IP2 Type]@row = "Ill", [IP2 Type]@row = "Injured"), [IP2 GS Affiliation]@row = "Staff", [IP2 Did incident occur while working?]@row = "Yes")), AND(OR([IP3 Type]@row = "Ill", [IP3 Type]@row = "Injured"), [IP3 GS Affiliation]@row = "Staff", [Did Incident Occur while IP3 was working? = "Yes")), AND(OR([IP4 Type]@row = "Ill", [IP4 Type]@row = "Injured"), [IP4 GS Affiliation]@row = "Staff", [Did Incident Occur while IP4 was working?]@row = "Yes")), "True")

I obviously don't understand how the formulas syntax works. Also, I forgot the IPx field is multi select. Is there a way to switch the = to contains? Would that work?

Again, much gratitude!

MattGS

I figured it out!! Thank you again for your quick and effective help!

Paul Newcome

Happy to help. 👍️

For anyone else that is wondering, here is a breakdown of the syntax:

IF(OR(AND(OR([IP1 Type]@row = "Ill", [IP1 Type]@row = "Injured"), [IP1 GS Affiliation]@row = "Staff", [Did Incident Occur while IP1 was working?]@row = 1), AND(OR([IP2 Type]@row = "Ill", [IP2 Type]@row = "Injured"), [IP2 GS Affiliation]@row = "Staff", [Did Incident Occur while IP2 was working?]@row = 1)), 1)

Person 1: Type is Ill OR Injured

OR([IP1 Type]@row = "Ill", [IP1 Type]@row = "Injured")

Person 1: Type (above) is true AND Affiliation is Staff AND Working? is checked

AND(OR(....., .....), [IP1 GS Affiliation]@row = "Staff", [Did Incident Occur while IP1 was working?]@row = 1)

Repeat the above for person 2

AND(OR(....., .....), ....., .....)

Nest them both in an OR.

OR(person 1, person 2)

OR(AND(OR(P1 Type, P1 Type), P1 Affiliation, P1 working?), AND(OR(P2 Type, P2 Type), P2 Affiliation, P2 working?))

Repeat as needed within the outermost OR function. Then output for IF is 1 to check the box if the AND/OR combo is true for any of the people.