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

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

Find more posts tagged with

Formulas

Comments

Andrée Starå

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

John M Lee

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.

Andrée Starå

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

Andrée Starå

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

Mike Wilday

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.

John M Lee

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!

Andrée Starå

Great!

I'm always happy to help!

Best,

Andrée

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…