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
-
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!
-
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)
-
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!
-
I figured it out!! Thank you again for your quick and effective help!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!