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

  • Genevieve P.
    Genevieve P. Employee
    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

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    edited 05/12/22

    @JGriffin

    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

  • Genevieve P.
    Genevieve P. Employee
    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

  • Mike TV
    Mike TV ✭✭✭✭✭✭

    @JGriffin

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!