We help IT Professionals succeed at work.

Error in compicated sum needs fixing

Simonrepro
Simonrepro asked
on
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
Comment
Watch Question

CERTIFIED EXPERT

Commented:
The #ref is coming from Sales tab C11 - based on other formulas in the column it should be SUM(X11/$X9) but I doubt this is correct. It looks like extra lines have been added here and, at a guess, overwritten a total that used to be in X9 but should now be in X12 with all formulas in column y adjusted accordingly

reg
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
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! :-)

Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
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

Author

Commented:
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
Excel VBA Developer
CERTIFIED EXPERT
Top Expert 2014
Commented:
Attached...I highlighted cells in the Sales and Budget worksheets that I changed.

Still returns a #VALUE! error on the Order sheet as I expected it would.  This is because the two arrays referenced in the SUMPRODUCT portion of the formula:
(Budgets!A$4:A$103=A3)*(Budgets!B$2:AB$2>=C$2)*(Budgets!B$2:AB$2<=D$2)
Budgets!B$4:AB$110

are not the same dimension.
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
whoops...here it is. TEST.xls
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
Not for points

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

regards, barry
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

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

Author

Commented:
yes you can close it

Explore More ContentExplore courses, solutions, and other research materials related to this topic.