Formula too long

[DashboardNovice]

I have the following formula:

=SUMPRODUCT(('Overall Depreciation Schedule'!E6:E44='2012 - Depreciation'!E6)*('Overall Depreciation Schedule'!M5:DP5<='2012 - Depreciation'!X5)*('Overall Depreciation Schedule'!M6:DP44))

I get a #VALUE! error.

Also, when I select the 3rd array and press F9 to evaluate it, I get a message stating that "Formula is too long. Formulas should not be longer than 8192 characters."

What am I doing wrong?

 

[Hui]

It looks ok
Are you able to post the file ?

 

[Hui]

Can you try this simple modification
=SUMPRODUCT(('Overall Depreciation Schedule'!E6:E44='2012 - Depreciation'!E6) * ('Overall Depreciation Schedule'!M5:DP5<='2012 - Depreciation'!X5) * 'Overall Depreciation Schedule'!M6:DP44)

I removed the brackets around the last range

 

[Hui]

Can you also try naming the ranges so that the formula becomes:
=SUMPRODUCT((ODS_Side='2012 - Depreciation'!E6) * (ODS_Top<='2012 - Depreciation'!X5) * ODS_Body)

where
ODS_Side = 'Overall Depreciation Schedule'!E6:E44
ODS_Top = 'Overall Depreciation Schedule'!M5:DP5
ODS_Body = 'Overall Depreciation Schedule'!M6:DP44

 

[NARAYANK991]

Hi ,

There is no problem with the formula ; more likely one of the cells in the data ranges referred to in the formula has an error value.

Narayan

 

[Hui]

If that doesn't help
Can you also try naming the ranges so that the formula becomes:

=SUMPRODUCT(--myMatrix)

where
myMatrix = (ODS_Side='2012 - Depreciation'!E6) * (ODS_Top<='2012 - Depreciation'!X5) * ODS_Body

ODS_Side = 'Overall Depreciation Schedule'!E6:E44
ODS_Top = 'Overall Depreciation Schedule'!M5:DP5
ODS_Body = 'Overall Depreciation Schedule'!M6:DP44

 

[Haseeb A]

Hello,

Looks like you have at least 1 'text value' or an #VALUE! erro in the range M6:DP44

Try the to remove last * in the formula & replace with a comma. If you have only text values in the range NOT error, this should work.

=SUMPRODUCT(('Overall Depreciation Schedule'!E6:E44='2012 - Depreciation'!E6)*('Overall Depreciation Schedule'!M5:DP5<='2012 - Depreciation'!X5),'Overall Depreciation Schedule'!M6:DP44)

Note: There is a comma after X5)

 

[DashboardNovice]

To all,

Thank you everybody for your input. I have found a solution. For those of you that would like to know what I did, I used the OFFSET function, which returns an array. Here is the my final formula:

=IFERROR(SUM(OFFSET('Overall Depreciation Schedule'!M$5,MATCH(E6,Asset_No.,0),0,1,MATCH(P$5,QuartersByYear,0))),"")

 

[edwards142]

Few days back when I got the same error in my Excel worksheet i have searched a lot about the issue because I don’t know why I am getting this error.
Now I know I am getting this error because the length of the formula contents has crossed the maximum limit of 8,192 characters.
So you should also check this first. Apart from this, for more fixes, you can check out this post.

source: Fix Excel Formula Is Too Long Error