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")
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!
Best Answer
-
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).
Answers
-
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","")))
-
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).
-
@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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!