I need to string together many conditions
I have 8 conditions I need to string together, if all are met the formula should return "yes" if anything any condition is false, the formula should return "". I have tried nesting If/ and statements a number of ways, but since they all need to work together I am coming up #unparsable with each iteration.
=IF(AND([Contract Signed]@row="YES",AND([Amount Owed]@row<=0,AND([Term 2 Complete?]@row="YES",AND([1st Rental Hours]@row<>"",AND([2nd Harvest Rent Hours Needed/Provided]@row="",AND([3rd Harvest Rent Hours Needed/Provided]@row="",AND([4th Harvest Rent Hours Needed/Provided]@row="",[5th Harvest Rent Hours Needed/Provided]@row<>""))))))),”YES",””)
Best Answer
-
Hi @JGriffin
I haven't tested your formula yet, but I noticed that you have a mixture of different types of quotation marks:
,”YES",””)
See how some of them are curved: ”
And some are straight: "
Smartsheet will need only straight ones, like so: ,"YES", "")
I'd suggest double-checking all your quotes in the formula to make sure it's the correct type. If you type directly into a Smartsheet cell, it will create the right type of quote. Sometimes copy/pasting from another page (e.g. from a Notepad) can change the quotes to be curved, so you'll want to type them in directly.
Let me know if this resolved the issue!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
You only need the first AND. It should look something like this:
=IF(AND([Contract Signed]@row="YES", [Amount Owed]@row<=0, [Term 2 Complete?]@row="YES", [1st Rental Hours]@row<>"", [2nd Harvest Rent Hours Needed/Provided]@row="", [3rd Harvest Rent Hours Needed/Provided]@row="", [4th Harvest Rent Hours Needed/Provided]@row="", [5th Harvest Rent Hours Needed/Provided]@row<>""),"YES", "")
-
That is how I started the formula, but I got the same #unparseable error
-
Hi @JGriffin
I haven't tested your formula yet, but I noticed that you have a mixture of different types of quotation marks:
,”YES",””)
See how some of them are curved: ”
And some are straight: "
Smartsheet will need only straight ones, like so: ,"YES", "")
I'd suggest double-checking all your quotes in the formula to make sure it's the correct type. If you type directly into a Smartsheet cell, it will create the right type of quote. Sometimes copy/pasting from another page (e.g. from a Notepad) can change the quotes to be curved, so you'll want to type them in directly.
Let me know if this resolved the issue!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
This looks to be working for me. All of my column types on the example sheet are text/number columns. Maybe yours aren't or maybe one or more are dropdown multi-select?
Formula:
=IF(AND([Contract Signed]@row = "YES", [Amount Owed]@row <= 0, [Term 2 Complete?]@row = "YES", [1st Rental Hours]@row <> "", [2nd Harvest Rent Hours Needed/Provided]@row = "", [3rd Harvest Rent Hours Needed/Provided]@row = "", [4th Harvest Rent Hours Needed/Provided]@row = "", [5th Harvest Rent Hours Needed/Provided]@row <> ""), "YES", "")
-
Thank you all! You are awesome. The issue ended up being a format problem with my quotation marks.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 459 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 137 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!