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

gnomer70 · 11/03/22

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

This is my ask:

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!

Paul Newcome · 11/04/22

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

Christian G. · 11/04/22

Hi @gnomer70

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

gnomer70 · 11/04/22

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

Paul Newcome · 11/04/22

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

Christian G. · 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","")))

Paul Newcome · 11/04/22

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

gnomer70 · 11/04/22

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

Paul Newcome · 11/07/22

Happy to help. 👍️

Formulas and Functions

Formulas and Functions

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

Best Answer

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

CountIF contains name from a contact list help needed

Formula for averaging days

Weekly rotating roles formulas