What is the maximum length of a formula within a cell?
The following is 4824 characters and it seems too long??
=IF([M1]$44 <> "",SUM(SUMIFS([M1]$54:[M1]$134, $[P&L Impacted]$54:$[P&L Impacted]$134, $[P&L Impacted]@row, $[nonEBITDA?]$54:$[nonEBITDA?]$134, false, $[Impact Name]$54:$[Impact Name]$134, "Forecast", $Currency$54:$Currency$134, "USD"), SUMIFS([M1]$54:[M1]$134, $[P&L Impacted]$54:$[P&L Impacted]$134, $[P&L Impacted]@row, $[nonEBITDA?]$54:$[nonEBITDA?]$134, false, $[Impact Name]$54:$[Impact Name]$134, "Forecast", $Currency$54:$Currency$134, "CAD") * IF(ISBLANK(INDEX({Conversion Rate Table  Rate}, MATCH([M1]$10, {Conversion Rate Table  Month}, 0))), INDEX(COLLECT({Conversion Rate Table  Rate}, {Conversion Rate Table  Rate}, <>""), COUNT(COLLECT({Conversion Rate Table  Rate}, {Conversion Rate Table  Rate}, <>""))), INDEX({Conversion Rate Table  Rate}, MATCH([M1]$10, {Conversion Rate Table  Month}, 0))), SUMIFS([M1]$54:[M1]$134, $[P&L Impacted]$54:$[P&L Impacted]$134, $[Cost Avoidance]@row, $[nonEBITDA?]$54:$[nonEBITDA?]$134, false, $[Impact Name]$54:$[Impact Name]$134, "Forecast", $Currency$54:$Currency$134, "USD"), SUMIFS([M1]$54:[M1]$134, $[P&L Impacted]$54:$[P&L Impacted]$134, $[Cost Avoidance]@row, $[nonEBITDA?]$54:$[nonEBITDA?]$134, false, $[Impact Name]$54:$[Impact Name]$134, "Forecast", $Currency$54:$Currency$134, "CAD") * IF(ISBLANK(INDEX({Conversion Rate Table  Rate}, MATCH([M1]$10, {Conversion Rate Table  Month}, 0))), INDEX(COLLECT({Conversion Rate Table  Rate}, {Conversion Rate Table  Rate}, <>""), COUNT(COLLECT({Conversion Rate Table  Rate}, {Conversion Rate Table  Rate}, <>""))), INDEX({Conversion Rate Table  Rate}, MATCH([M1]$10, {Conversion Rate Table  Month}, 0))), SUMIFS([M1]$54:[M1]$134, $[P&L Impacted]$54:$[P&L Impacted]$134, $[Type of Impact]@row, $[nonEBITDA?]$54:$[nonEBITDA?]$134, false, $[Impact Name]$54:$[Impact Name]$134, "Forecast", $Currency$54:$Currency$134, "USD"), SUMIFS([M1]$54:[M1]$134, $[P&L Impacted]$54:$[P&L Impacted]$134, $[Type of Impact]@row, $[nonEBITDA?]$54:$[nonEBITDA?]$134, false, $[Impact Name]$54:$[Impact Name]$134, "Forecast", $Currency$54:$Currency$134, "CAD") * IF(ISBLANK(INDEX({Conversion Rate Table  Rate}, MATCH([M1]$10, {Conversion Rate Table  Month}, 0))), INDEX(COLLECT({Conversion Rate Table  Rate}, {Conversion Rate Table  Rate}, <>""), COUNT(COLLECT({Conversion Rate Table  Rate}, {Conversion Rate Table  Rate}, <>""))), INDEX({Conversion Rate Table  Rate}, MATCH([M1]$10, {Conversion Rate Table  Month}, 0)))),SUM(SUMIFS([M1]$54:[M1]$134, $[P&L Impacted]$54:$[P&L Impacted]$134, $[P&L Impacted]@row, $[nonEBITDA?]$54:$[nonEBITDA?]$134, false, $[Impact Name]$54:$[Impact Name]$134, "Planned", $Currency$54:$Currency$134, "USD"), SUMIFS([M1]$54:[M1]$134, $[P&L Impacted]$54:$[P&L Impacted]$134, $[P&L Impacted]@row, $[nonEBITDA?]$54:$[nonEBITDA?]$134, false, $[Impact Name]$54:$[Impact Name]$134, "Planned", $Currency$54:$Currency$134, "CAD") * IF(ISBLANK(INDEX({Conversion Rate Table  Rate}, MATCH([M1]$10, {Conversion Rate Table  Month}, 0))), INDEX(COLLECT({Conversion Rate Table  Rate}, {Conversion Rate Table  Rate}, <>""), COUNT(COLLECT({Conversion Rate Table  Rate}, {Conversion Rate Table  Rate}, <>""))), INDEX({Conversion Rate Table  Rate}, MATCH([M1]$10, {Conversion Rate Table  Month}, 0))), SUMIFS([M1]$54:[M1]$134, $[P&L Impacted]$54:$[P&L Impacted]$134, $[Cost Avoidance]@row, $[nonEBITDA?]$54:$[nonEBITDA?]$134, false, $[Impact Name]$54:$[Impact Name]$134, "Planned", $Currency$54:$Currency$134, "USD"), SUMIFS([M1]$54:[M1]$134, $[P&L Impacted]$54:$[P&L Impacted]$134, $[Cost Avoidance]@row, $[nonEBITDA?]$54:$[nonEBITDA?]$134, false, $[Impact Name]$54:$[Impact Name]$134, "Planned", $Currency$54:$Currency$134, "CAD") * IF(ISBLANK(INDEX({Conversion Rate Table  Rate}, MATCH([M1]$10, {Conversion Rate Table  Month}, 0))), INDEX(COLLECT({Conversion Rate Table  Rate}, {Conversion Rate Table  Rate}, <>""), COUNT(COLLECT({Conversion Rate Table  Rate}, {Conversion Rate Table  Rate}, <>""))), INDEX({Conversion Rate Table  Rate}, MATCH([M1]$10, {Conversion Rate Table  Month}, 0))), SUMIFS([M1]$54:[M1]$134, $[P&L Impacted]$54:$[P&L Impacted]$134, $[Type of Impact]@row, $[nonEBITDA?]$54:$[nonEBITDA?]$134, false, $[Impact Name]$54:$[Impact Name]$134, "Planned", $Currency$54:$Currency$134, "USD"), SUMIFS([M1]$54:[M1]$134, $[P&L Impacted]$54:$[P&L Impacted]$134, $[Type of Impact]@row, $[nonEBITDA?]$54:$[nonEBITDA?]$134, false, $[Impact Name]$54:$[Impact Name]$134, "Planned", $Currency$54:$Currency$134, "CAD") * IF(ISBLANK(INDEX({Conversion Rate Table  Rate}, MATCH([M1]$10, {Conversion Rate Table  Month}, 0))), INDEX(COLLECT({Conversion Rate Table  Rate}, {Conversion Rate Table  Rate}, <>""), COUNT(COLLECT({Conversion Rate Table  Rate}, {Conversion Rate Table  Rate}, <>""))), INDEX({Conversion Rate Table  Rate}, MATCH([M1]$10, {Conversion Rate Table  Month}, 0)))))
Best Answer

There is a 4K character limit on formulas. Try a abbreviating your references to bring it down in size or simplify by using several columns to break up the calculations and then using a formula to consolidate.
For Example: Instead of using {Conversion Rate Table  Month} as a reference name, try using something like {CRT  Month}
I hope this helps.
Cheers,
Ramzi
Ramzi Khuri  Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
Answers

There is a 4K character limit on formulas. Try a abbreviating your references to bring it down in size or simplify by using several columns to break up the calculations and then using a formula to consolidate.
For Example: Instead of using {Conversion Rate Table  Month} as a reference name, try using something like {CRT  Month}
I hope this helps.
Cheers,
Ramzi
Ramzi Khuri  Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

Thank you for your response  I'm implementing now...........however I noticed the next issue with my formula.......
In the "SUM(SUMIFS([M1]$54:[M1]$134, ...." the formula is being put into a cell in the M1 column. I want to repeat the process in M2 column, M3 ....... M48 column and had hoped to use a global update (as I have 100s of sheets to add this to).
Is there special code to allow the column name to be dynamic and to move with and correspond to the same column that the formula is being placed?
Thanks!😚

As far as you have not locked your column in the formula (as in $[M1], etc. you should be able to drag copy the formula across all columns and it will automatically take on the columns it's in.
Are you using Control Center? You referred to "global update" so I thought I would ask. If you are, that may be a bit more challenging if you are trying to do this in bulk.
Cheers,
Ramzi
Ramzi Khuri  Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!