Error in compicated sum needs fixing

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

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

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

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

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

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

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

yes you can close it