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

Options
edited 12/09/19

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:

• ✭✭✭✭✭✭
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.

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

• ✭✭✭✭✭✭
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.

• ✭✭✭✭✭✭
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.

• ✭✭✭✭✭✭
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.

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

• ✭✭✭✭✭✭
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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!