IF(OR) formula - add AND qualifier for all

MRL · 03/15/24

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.

Jason Tarpinian · 03/15/24

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 · 03/15/24

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)

MRL · 03/15/24

https://community.smartsheet.com/discussion/comment/425537#Comment_425537

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.

Jason Tarpinian · 03/15/24

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))

MRL · 03/15/24

https://community.smartsheet.com/discussion/comment/425546#Comment_425546

That did it! Thank you so much! This formula model will come in handy in other instances, too. Thank you!

Jason Tarpinian · 03/15/24

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 :)

IF(OR) formula - add AND qualifier for all

Best Answer

Answers

Help Article Resources

Categories