Totalling/consolidating data

Hi,

On the Total sheet how do you get a closing stock in N5 of 1259549. Starting with a closing stock in M5 of 135206
and demand in N3 of 10000 its should surely be 125206.

A more fundamental question is are there always three rows on the Total Sheet for each pair of three rows on the Product level sheet, AND are they always in the same order so that the actual Material ID refs can be ignored. i.e. does row Total row 3 always sum rows Product rows 3 & 6, and row 4 sum 4&7..

Then Row 6 sum rows 9 & 12, and row 7 rows 10 & 13.

If so then holding a permanent formulae on the Total sheet would seem the simplest way.

That said, and whilst it might not be a problem at the moment, you are committing the cardinal sin of merging cells. Most of us avoid them like the plagues since they cause too many problems.

Is the Material ID in the Product Level sheet column A always 5 digits and ID new in column B always 4 digits.


If this is a much simplified example and doesn't represent the size of your actual data we often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

In which case please upload a file with some real data that. Be sure to include more than one set of paired values on the Product Level sheet and if there are IDs of different lengths that there are examples.

...and the important question is are the codes always the same length and always numerics? And if a helper column would help simplify formulae is that permitted?

If I understand correctly then the following may work.
For the Demand rows: =SUMPRODUCT(('Product Level'!L5:L17)*(('Product Level'!$A5:$A17=$A5)+('Product Level'!$A5:$A17=$B5)))
For the Production rows: =SUMPRODUCT(('Product Level'!L6:L18)*(('Product Level'!$A5:$A17=$A5)+('Product Level'!$A5:$A17=$B5)))
Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.