Simonrepro

asked on

# Error in compicated sum needs fixing

Hi,

In the attached spreadsheet, worksheet 'Order', Cell E3 is a sum which has stopped working, would you mind checking it out for me please?

Regards

Simon

TEST.xls

In the attached spreadsheet, worksheet 'Order', Cell E3 is a sum which has stopped working, would you mind checking it out for me please?

Regards

Simon

TEST.xls

E3 contains no formula. Do you mean cell C3?

At first glance, it appears that the resultant array from the first part of the SUMPRODUCT function is not the same dimension as the second array. That is where the first #REF! error is occurring (although I would have expected a #VALUE! error instead).

Can you please describe what you are trying to show in this column? It is indeed quite complicated! :-)

At first glance, it appears that the resultant array from the first part of the SUMPRODUCT function is not the same dimension as the second array. That is where the first #REF! error is occurring (although I would have expected a #VALUE! error instead).

Can you please describe what you are trying to show in this column? It is indeed quite complicated! :-)

Oh...the Budgets sheet is in need of overhaul. It used references to the Sales sheet as a basis for budgets but is all fouled up from columns W-AB.

Additionally, those references are not aligned to the Stock and Sales sheet. The Stock sheet only contains 12 months of data (Fiscal Year runs from April-March), but the Sales and Budget sheets are considerably longer in period. In fact, the values for November and December on the Budget sheet were manually overwritten with values while the following months retained the original formula (=SUM(Sales!xxx)).

By the way, that SUM function is not needed; only need to reference the original cell like so:

=Sales!xxx

Additionally, those references are not aligned to the Stock and Sales sheet. The Stock sheet only contains 12 months of data (Fiscal Year runs from April-March), but the Sales and Budget sheets are considerably longer in period. In fact, the values for November and December on the Budget sheet were manually overwritten with values while the following months retained the original formula (=SUM(Sales!xxx)).

By the way, that SUM function is not needed; only need to reference the original cell like so:

=Sales!xxx

ASKER

Hi Glenn,

Is there any chance you can put some dummy data in and fix the sum and I can then sort my one out?

Kind Regards

Simon

Is there any chance you can put some dummy data in and fix the sum and I can then sort my one out?

Kind Regards

Simon

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**
whoops...here it is. TEST.xls

Not for points

Presumably the 103 should be changed to 110 (or vice versa) - that will fix the SUMPRODUCT

regards, barry

Presumably the 103 should be changed to 110 (or vice versa) - that will fix the SUMPRODUCT

regards, barry

Barry's correct...changing that removes the error. The formula unfortunately now just returns zero (0) throughout.

I'll examine it further, but hard to diagnose when I'm not completely sure what this value is supposed to represent.

-Glenn

I'll examine it further, but hard to diagnose when I'm not completely sure what this value is supposed to represent.

-Glenn

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

ASKER

yes you can close it

reg