# IF(OR) formula - add AND qualifier for all

Options
✭✭

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.

Tags:

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!