Stumped! How to Write IF, AND, OR With Multiple Conditions?
Hello! I'm trying to write a column formula that will return "YES" if the determined value is "Approved" or "Not Applicable", and return "NO" otherwise.
My formula needs to read across 8 columns, and each column is a dropdown list restricted to 4 values. If all of the columns are values "Not Applicable," or "Approved,", then it should return "YES," however if any of columns are values "Submitted," or "Rejected," then it must return "NO"
"Not Applicable"
"Submitted"
"Approved"
"Rejected"
So with the above in mind, I wrote a nested IF(AND(OR formula, see golden child formula, and it worked great! However once I included all of the columns, see attempt one, Smartsheet warns of a sytanx error and stops the formula from running. I tried adding each piece of the formula one by one, to no avail.
After reading through the help forums, I came upon a user with a similar issue and followed the advice they were given, see attempt two, however this formula fails on me by returning "YES" regardless of the value chosen. I am not familiar with the CONTAINS( function, so I may just be spectaturely messing it up.
I've been staring at smartsheet and notepad for far too long trying to solve this, and I'm quite stumped.
Am I just overthinking? I may be overthinking.
Any recommendations are greatly appreciated.
Golden Child Formula:
=IF(AND(OR([Creative Services Department Coordinator Response]@row = "Approved", [Creative Services Department Coordinator Response]@row = "Not Applicable"), (OR([Engineering Department Coordinator Response]@row = "Approved", [Engineering Department Coordinator Response]@row = "Not Applicable"))), "YES", "NO")
Attempt One:
=IF(AND(OR([Creative Services Department Coordinator Response]@row = "Approved", [Creative Services Department Coordinator Response]@row = "Not Applicable"), (OR([Engineering Department Coordinator Response]@row = "Approved", [Engineering Department Coordinator Response]@row = "Not Applicable"), (OR([Executive Department Coordinator Response]@row = "Approved", [Executive Department Coordinator Response]@row = "Not Applicable"), (OR([Marketing Department Coordinator Response]@row = "Approved", [Marketing Department Coordinator Response]@row = "Not Applicable"), (OR([Quality Department Coordinator Response]@row = "Approved", [Quality Department Coordinator Response]@row = "Not Applicable"), (OR([Regulatory Department Coordinator Response]@row = "Approved", [Regulatory Department Coordinator Response]@row = "Not Applicable"), (OR([Sourcing Department Coordinator Response]@row = "Approved", [Sourcing Department Coordinator Response]@row = "Not Applicable"), (OR([Supply Chain Department Coordinator Response]@row = "Approved", [Supply Chain Department Coordinator Response]@row = "Not Applicable"))))))))), "YES", "NO")
Attempt Two:
=IF(OR(CONTAINS("Approved", [Creative Services Department Coordinator Response]@row), CONTAINS("Not Applicable", [Creative Services Department Coordinator Response]@row), CONTAINS("Approved", [Engineering Department Coordinator Response]@row),CONTAINS("Not Applicable", [Engineering Department Coordinator Response]@row), CONTAINS("Approved", [Executive Department Coordinator Response]@row), CONTAINS("Not Applicable", [Executive Department Coordinator Response]@row),CONTAINS("Approved", [Marketing Department Coordinator Response]@row), CONTAINS("Not Applicable", [Marketing Department Coordinator Response]@row), CONTAINS("Approved", [Quality Department Coordinator Response]@row), CONTAINS("Not Applicable", [Quality Department Coordinator Response]@row), CONTAINS("Approved", [Regulatory Department Coordinator Response]@row), CONTAINS("Not Applicable", [Regulatory Department Coordinator Response]@row), CONTAINS("Approved", [Sourcing Department Coordinator Response]@row), CONTAINS("Not Applicable", [Sourcing Department Coordinator Response]@row), CONTAINS("Approved", [Supply Chain Department Coordinator Response]@row), CONTAINS("Not Applicable", [Supply Chain Department Coordinator Response]@row)), "YES", "NO")
Best Answer
-
You have an extra paraenthesis in front of your ORs. You keep typing (OR( but it should be OR(
(for attempt 1)
Answers
-
You have an extra paraenthesis in front of your ORs. You keep typing (OR( but it should be OR(
(for attempt 1)
-
I keep trying to post the full formula but it's not working for some reason
-
Ah! That fixed it, thank you so much! Once you pointed it out I saw exactly what you meant and pulling all the extra parenthesis out fixed it perfectly.
I've run a couple tests and it's returning everything correctly.
(I'm guessing it won't let you post because it runs past the character count for the comments? )
-
If it'll let me post: Here's the fixed formula for anyone who may find it useful!
=IF(AND(OR([Creative Services Department Coordinator Response]@row = "Approved", [Creative Services Department Coordinator Response]@row = "Not Applicable"), OR([Engineering Department Coordinator Response]@row = "Approved", [Engineering Department Coordinator Response]@row = "Not Applicable"), OR([Executive Department Coordinator Response]@row = "Approved", [Executive Department Coordinator Response]@row = "Not Applicable"), OR([Marketing Department Coordinator Response]@row = "Approved", [Marketing Department Coordinator Response]@row = "Not Applicable"), OR([Quality Department Coordinator Response]@row = "Approved", [Quality Department Coordinator Response]@row = "Not Applicable"), OR([Regulatory Department Coordinator Response]@row = "Approved", [Regulatory Department Coordinator Response]@row = "Not Applicable"), OR([Sourcing Department Coordinator Response]@row = "Approved", [Sourcing Department Coordinator Response]@row = "Not Applicable"), OR([Supply Chain Department Coordinator Response]@row = "Approved", [Supply Chain Department Coordinator Response]@row = "Not Applicable")), "YES", "NO")
-
Sometimes we just need an extra pair of eyes :)
-
Try this
=IF(AND(OR([Creative Services Department Coordinator Response]@row = "Approved", [Creative Services Department Coordinator Response]@row = "Not Applicable"), OR([Engineering Department Coordinator Response]@row = "Approved", [Engineering Department Coordinator Response]@row = "Not Applicable"), OR([Executive Department Coordinator Response]@row = "Approved", [Executive Department Coordinator Response]@row = "Not Applicable"), OR([Marketing Department Coordinator Response]@row = "Approved", [Marketing Department Coordinator Response]@row = "Not Applicable"), OR([Quality Department Coordinator Response]@row = "Approved", [Quality Department Coordinator Response]@row = "Not Applicable"), OR([Regulatory Department Coordinator Response]@row = "Approved", [Regulatory Department Coordinator Response]@row = "Not Applicable"), OR([Sourcing Department Coordinator Response]@row = "Approved", [Sourcing Department Coordinator Response]@row = "Not Applicable"), OR([Supply Chain Department Coordinator Response]@row = "Approved", [Supply Chain Department Coordinator Response]@row = "Not Applicable"))))))))), "YES", "NO")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!