Multiple criteria for If formula

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

Answers

  • Eric Law
    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
    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
    MattGS ✭✭
    edited 06/08/23

    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
    MattGS ✭✭

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

  • Paul Newcome
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!