# 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)))))

• ✭✭✭

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