Checkbox formula using =IF(OR

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

Find more posts tagged with

Formulas

Project Management

Comments

Paul Newcome

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)

IBRIEOG

Perfect - Thank you for your explanation and solution

Paul Newcome

Happy to help!

Issa M

Hi @Paul Newcome

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!

Paul Newcome

@Issa M

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)

Gail Friedman

@Paul Newcome

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.

Thank you for any help you can provide

Paul Newcome

@Gail Friedman

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

Gail Friedman

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

Paul Newcome

@Gail Friedman Happy to help! 👍️

Issa M

Awesome. Thank you, Paul!

Paul Newcome

@Issa M Happy to help! 👍️

Christina Oliver

@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

Paul Newcome

@Christina Oliver Try this instead...

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

Christina Oliver

@Paul Newcome THANK YOU!!

Paul Newcome

@Christina Oliver Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Issue with automation process reporting a date in a special column
In the Smartsheet sheet “CRS RO Master” we have programmed an automation called “Track Changes in dates, confirmation and LHG code” [PICTURE 1] This one should report a date of a change in the columns “CRS GO LIVE DATE” and “PMS Go LIVE DATE” in the column “. CRS Check date changed”. [PICTURE 2] The update of the “Go live…