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.

Find more posts tagged with

Formulas

Accepted answers

Jason Tarpinian

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

All comments

Jason Tarpinian

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

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

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

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.