IF(OR) formula - add AND qualifier for all
I have an IF(OR) formula that is working exactly the way I want it to. I want it to check a box if one of three column criteria is met, which it is doing correctly, using the below formula:
=IF(OR([Column A]@row = "Choice1", [Column B]@row = "Choice2", [Column C]@row = 1)), 1
I want to add a second condition that applies to all criteria. If Column D equals "Deleted," then I do not want the box checked for that row, even if one of the correct criteria appears in Columns A-C.
Is there a way to add an overall AND to my IF(OR)? Or another way to include that information?
Essentially what I want is this: [Column D]@row, <>"Deleted" but I don't know how to fit it in.
Best Answer
-
My bad, tried doing it too quickly in my head. Syntax is important!
=IF([Column D]@row = "Deleted", "", IF(OR([Column A]@row = "Choice1", [Column B]@row = "Choice2", [Column C]@row = 1), 1))
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
Answers
-
You can build a nested IF (IF within an IF) You would just add the "Deleted" as your first condition, so that if the first condition is met, the rest of the formula won't even matter.
=IF([Column D]@row = "Deleted", "", IF(OR([Column A]@row = "Choice1", [Column B]@row = "Choice2", [Column C]@row = 1)), 1)
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
Thank you for the quick reply, Jason, but I'm getting #INCORRECT ARGUMENT when I add =IF([Column D]@row = "Deleted", "", to the front of my existing formula. The column name is turning a color, as it does when it is aligned correctly to a column, so I know I don't have a typo or other error with my column reference.
-
My bad, tried doing it too quickly in my head. Syntax is important!
=IF([Column D]@row = "Deleted", "", IF(OR([Column A]@row = "Choice1", [Column B]@row = "Choice2", [Column C]@row = 1), 1))
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
That did it! Thank you so much! This formula model will come in handy in other instances, too. Thank you!
-
No problem! I use it a lot with my internal teams on "Status" for tasks in a schedule. You just prioritize the logic with the IF statements, so we can auto-status a task based on Complete %, Start Date/End Date, and then call it Complete, Overdue, In Progress, Late, Not Started... you can use as many IFs as your brain desires :)
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!