Is there a limit to the length of If/And Statements?

Options
John M Lee
John M Lee ✭✭
edited 12/09/19 in Formulas and Functions

I have the following statement and it works for the bold section but when I add the italic section I receive an 'UNPARSABLE' error.  Thoughts?

=IF(AND([From Calculator]2 = "<100", [From Calculator]4 = "<25"), [Column2]2, IF(AND([From Calculator]2 = "<100", [From Calculator]4 = "26-50"), [Column3]2, IF(AND([From Calculator]2 = "<100", [From Calculator]4 = "51-100"), [Column4]2,IF(AND([From Calculator]2 = "<100", [From Calculator]4 = ">100"), [Column5]2, IF(AND([From Calculator]2 = "101-250", [From Calculator]4 = "<25"), [Column2]3, IF(AND([From Calculator]2 = "101-250", [From Calculator]4 = "26-50"), [Column3]3, IF(AND([From Calculator]2 = "101-250", [From Calculator]4 = "51-100"), [Column4]3, IF(AND([From Calculator]2 = "101-250", [From Calculator]4 = ">100"), [Column5]3, IF(AND([From Calculator]2 = "251-500", [From Calculator]4 = “<25”),[Column2]4, IF(AND([From Calculator]2 = "251-500", [From Calculator]4 = “26-50”),[Column3]4, IF(AND([From Calculator]2 = "251-500", [From Calculator]4 = “51-100”),[Column4]4, IF(AND([From Calculator]2 = "251-500", [From Calculator]4 = “>100”), [Column5]4))))))))))))

I have 2 more statement sections I would like to add if possible.

IF(AND([From Calculator]2 = "500-1,000", [From Calculator]4 = “<25”),[Column2]5, IF(AND([From Calculator]2 = "500-1,000", [From Calculator]4 = “26-50”),[Column3]5, IF(AND([From Calculator]2 = "500-1,000", [From Calculator]4 = “51-100”),[Column4]5, IF(AND([From Calculator]2 = "500-1,000", [From Calculator]4 = “>100”),[Column5]5, IF(AND([From Calculator]2 = "1,001-2,500", [From Calculator]4 = “<25”),[Column2]6, IF(AND([From Calculator]2 = "1,001-2,500", [From Calculator]4 = “26-50”),[Column3]6, IF(AND([From Calculator]2 = "1,001-2,500", [From Calculator]4 = “51-100”),[Column4]6, IF(AND([From Calculator]2 = "1,001-2,500", [From Calculator]4 = “>100”),[Column5]6

Tags:
· Share on FacebookShare on Twitter

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi John,

    If I remember correctly, the limit is 4000 characters.

    Hope that helps!

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

    · Share on FacebookShare on Twitter
  • John M Lee
    John M Lee ✭✭
    Options

    Thank you for the quick response but the character count above is 973 with spaces. I can't figure out what is wrong if anything.

    · Share on FacebookShare on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Happy to help!

    Could you maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too, andree@getdone.se)

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

    · Share on FacebookShare on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    I think I found the error. You had a few characters that were wrong.

    “” and I replaced them with ".

    Try this.

    =IF(AND([From Calculator]2 = "<100", [From Calculator]4 = "<25"), [Column2]2, IF(AND([From Calculator]2 = "<100", [From Calculator]4 = "26-50"), [Column3]2, IF(AND([From Calculator]2 = "<100", [From Calculator]4 = "51-100"), [Column4]2,IF(AND([From Calculator]2 = "<100", [From Calculator]4 = ">100"), [Column5]2, IF(AND([From Calculator]2 = "101-250", [From Calculator]4 = "<25"), [Column2]3, IF(AND([From Calculator]2 = "101-250", [From Calculator]4 = "26-50"), [Column3]3, IF(AND([From Calculator]2 = "101-250", [From Calculator]4 = "51-100"), [Column4]3, IF(AND([From Calculator]2 = "101-250", [From Calculator]4 = ">100"), [Column5]3,IF(AND([From Calculator]2 = "251-500", [From Calculator]4 = "<25"),[Column2]4, IF(AND([From Calculator]2 = "251-500", [From Calculator]4 = "26-50"),[Column3]4, IF(AND([From Calculator]2 = "251-500", [From Calculator]4 = "51-100"),[Column4]4, IF(AND([From Calculator]2 = "251-500", [From Calculator]4 = ">100"), [Column5]4))))))))))))

    I have 2 more statement sections I would like to add if possible.

    IF(AND([From Calculator]2 = "500-1,000", [From Calculator]4 = "<25"),[Column2]5, IF(AND([From Calculator]2 = "500-1,000", [From Calculator]4 = "26-50"),[Column3]5, IF(AND([From Calculator]2 = "500-1,000", [From Calculator]4 = "51-100"),[Column4]5, IF(AND([From Calculator]2 = "500-1,000", [From Calculator]4 = ">100"),[Column5]5, IF(AND([From Calculator]2 = "1,001-2,500", [From Calculator]4 = "<25"),[Column2]6, IF(AND([From Calculator]2 = "1,001-2,500", [From Calculator]4 = "26-50"),[Column3]6, IF(AND([From Calculator]2 = "1,001-2,500", [From Calculator]4 = "51-100"),[Column4]6, IF(AND([From Calculator]2 = "1,001-2,500", [From Calculator]4 = ">100"),[Column5]6

    Did it work?

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

    · Share on FacebookShare on Twitter
  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    Yes, the issue was that somewhere down the line Smart Quotes were inserted instead of straight quotes. Always compose your formulas in a basic text editor. Word and Google Docs adds a different quote to your formulas which break smartseheet. :) 

    · Share on FacebookShare on Twitter
  • John M Lee
    John M Lee ✭✭
    Options

    This worked!  Thank you very much.  I was writing the statement in Word and didn't realize it was converting the style/angle of those double quotes.

     

    Thanks!

    · Share on FacebookShare on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Great!

    I'm always happy to help!

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!