Hi Everyone--I am trying to create a multiple IF statement that is coming an "or" and a "contains" .

✭✭

This is what I have. It works independently as single statements but combined I get the dreaded UNPARSEABBLE.

=IF(OR(temp@row = “Ambient”, Temp@row = “None Required”), “WIP”), IF (Temp@row = "2-8c cooler", "COL"), IF(CONTAINS(Temp@row, "Freezer"), "FRE")

If the column TEMP (in this row) says "Ambient" or "None Required" the result is WIP, IF the Temp (in this row) is "2-8c Cooler" the result is COL, IF the TEMP(in this row) contains the word "Freezer" the result is FRE.

Any help is appreciated!

• ✭✭✭✭✭✭

Once you fix the parenthesis issue, the syntax should be correct.

The problem then becomes some of your quotes...

See the slanted ones? Those are called "Smart Quotes" which (ironically enough) Smartsheet does not recognize as a valid character in formulas. You are going to need to retype them either here in the community, in Smartsheet directly, or in a text editor such as Notepad (not Word).

• ✭✭✭✭✭✭
edited 11/04/22

You are closing the IF's ")" to soon.

=IF(Condition, TRUE, FALSE )

for nested ifs, you need to replace the "False" with your other ifs.

ei. IF(Condition, TRUE, IF(Condition, TRUE, IF(Condition, TRUE, IF(Condition, TRUE, FALSE ) ) ) )

Try this one.

=IF(OR(temp@row = “Ambient”, Temp@row = “None Required”), “WIP”, IF(Temp@row = "2-8c cooler", "COL", IF(CONTAINS(Temp@row, "Freezer"), "FRE","")))

• ✭✭

Thanks @Christian G. --but I'm still getting unparseable. :/

• ✭✭✭✭✭✭

Once you fix the parenthesis issue, the syntax should be correct.

The problem then becomes some of your quotes...

See the slanted ones? Those are called "Smart Quotes" which (ironically enough) Smartsheet does not recognize as a valid character in formulas. You are going to need to retype them either here in the community, in Smartsheet directly, or in a text editor such as Notepad (not Word).

• ✭✭✭✭✭✭
edited 11/04/22

@gnomer70 Let's break it done so it's easier to parse !

if(OR(Temp@row = “Ambient”, Temp@row = “None Required”),"WIP", else...)

if(Temp@row = "2-8c cooler", "COL", else...)

if(Temp@row= "Freezer", "FRE","")

=if(OR(Temp@row = “Ambient”, Temp@row = “None Required”),"WIP",if(Temp@row = "2-8c cooler", "COL",if(Temp@row= "Freezer", "FRE","")))

Will there be the word "Freezer" with something else in the cell ?

"Contains" function is inverted.

try this

=IF(OR(temp@row = “Ambient”, Temp@row = “None Required”), “WIP”, IF(Temp@row = "2-8c cooler", "COL", IF(CONTAINS("Freezer",Temp@row), "FRE","")))

• ✭✭✭✭✭✭

@Christian G. Take a look at the quotes around Ambient and None Rquired.

• ✭✭

IT WORKS!

@Christian G. thank you for your help with the organization

@Paul Newcome thank you for pointing out the quotes "" and getting those in notepad. That was the final piece of the puzzle.

THANK YOU THANK YOU THANK YOU!!

• ✭✭✭✭✭✭

Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!