# Checkbox formula using =IF(OR

Options
✭✭✭✭
edited 12/09/19
Hi,

I am trying to find a formula to check a box based on another cell in the row having either maintenance OR support (along with other text) but keep getting an #INCORRECT ARGUMENT SET error.

The formula works perfectly for one criteria i.e. =IF(FIND("Maintenance", \$[Item description]@row) >= 1, 1, 0) but when I try to add the second FIND using the OR function I get an error message.

Please could someone advice where I am going wrong with this formula or if there is an alternative way to get to the desired outcome  - a checked box if either maintenance or support are present in the item description column

=IF(OR(FIND("Maintenance", \$[Item description]@row) >= 1, 1, 0, FIND("Support", \$[Item description]@row) >= 1, 1, 0))

Thank you
«1

• ✭✭✭✭✭✭
Options

That would be because of your syntax. When building an IF statement with an OR function, the OR will be your criteria, then value if true, then value if false.

=IF(logical_statement, value_if_true, [value_if_false])

The OR function also requires logical statements

OR(logical_statement_1, logical_statement_2)

So it gets nested in the first portion of the IF statement.

=IF(OR(this is true, or this is true), do this, otherwise do something else)

.

=IF(OR(FIND("Maintenance", \$[Item description]@row) >= 1, FIND("Support", \$[Item description]@row) >= 1), 1, 0)

• ✭✭✭✭
Options

Perfect - Thank you for your explanation and solution

• ✭✭✭✭✭✭
Options
• ✭✭✭
edited 01/30/20
Options

Thanks for the explanation above. I'm trying to adapt your formula for a scenario where I have 3 terms to search for but I keep getting an unparseable error message for some reason.

What if, for example, I want to accomplish the same exact thing as IBRIEOG except with a third word --"Rebuild"?

=IF(OR(FIND("Maintenance", \$[Item description]@row) >= 1, FIND("Support", \$[Item description]@row) >= 1), FIND("Rebuild", \$[Item description]@row) >= 1) 1, 0)

Why is this not working?

Thanks!

• ✭✭✭✭✭✭
Options

The problem is an extra closing parenthesis that closes out the OR function before your third FIND and a missing comma after the OR function closes out before you enter the "value if true" portion of the IF statement. Try this instead...

=IF(OR(FIND("Maintenance", \$[Item description]@row) >= 1, FIND("Support", \$[Item description]@row) >= 1, FIND("Rebuild", \$[Item description]@row) >= 1), 1, 0)

• Options

I have a similar situation where I am trying to change status health. I want the health color to change from green to yellow if a support ticket has been open more than 2 days and red if it has been open longer than 3 days. My formula keeps returning a value of #UNPARSEABLE. The formula I am using is:

=IF(OR([Issue Status]@row <> "Completed", TODAY() > ([Issue Created Date]@row + 2), "Yellow"', [Issue Status]@row <> "Completed", TODAY() > ([Issue Created Date]@row + 3), "Red", "Green")

It works with one value, but when I add the second (Red, Green) I get the error message.

• ✭✭✭✭✭✭
Options

It is because you need to open additional IF statements (I am also going to change your OR to an AND statement).

IF(AND([Issue Status]@row <> "Completed", TODAY() > ([Issue Created Date]@row + 3), "Red"

IF(AND([Issue Status]@row <> "Completed", TODAY() > ([Issue Created Date]@row + 2), "Yellow"

These are your two IF statements individually (the final "Green" output will be added in at the end).

When we nest them, we take the second IF and drop it into the "else" portion of the first IF then close them both out at the end of the formula. Give this a try and see how it works for you.

=IF(AND([Issue Status]@row <> "Completed", TODAY() > ([Issue Created Date]@row + 3), "Red", IF(AND([Issue Status]@row <> "Completed", TODAY() > ([Issue Created Date]@row + 2), "Yellow", "Green"))

• Options

Thank you for your quick response. I have it working.

• ✭✭✭✭✭✭
Options
• ✭✭✭
Options

Awesome. Thank you, Paul!

• ✭✭✭✭✭✭
Options

@Issa M Happy to help! 👍️

• Options

@Paul Newcome can you help me? I am trying to get a Done box checked if my Status column = Done or N/A.

Can you tell me what is wrong with my formula? =IF(OR(Status23= "Done"), 1, 0), Status23= "N/A", 1, 0

• ✭✭✭✭✭✭
Options

=IF(OR(Status@row= "Done", Status@row = "N/A"), 1, 0)

• Options

@Paul Newcome THANK YOU!!

• ✭✭✭✭✭✭
Options

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!