Need Sum total for Week to Date, Previous Week to Date , Month to Date and ....

Hi,

Whilst you can get the information you want with a mixture of INDEX() & Match() functions, and using a helper column to strip out the name of the Mill on both sheets, you are making a whole load of work for yourself.

You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it, Yours exhibits all those features.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

In this case your input sheet should contain columns for
Date
Mil Name
UOM
Production numbers.

Hi Richard:

Thank you for your detailed response, but trust me if I was able to use the pivot or power pivot functionality of EXCEL, I would have not posted this question. The attached spreadsheet is really a very simplified one line item data for input. However, the 'total' production for each mill comprises of many finished goods and then we have over 160 (rows) line items to report per the attached format for columns.

I was able to extract the report date and the previous date production numbers using the index and match function, however, am getting a mental block in getting the sum for WTD, MTD etc

Any help in this direction would be greatly appreciated.

Thanks again

Hi,

There seems an apparent contradiction but no doubt you can clarify. Your original contained two data rows showing two different mills. From that I implied that you were looking to pick a particular singular statistic for a particular mill for a particular day. Hence my mention of Index & Match - which you confirm you have tried.

However now you are mentioning that any one mill might have many rows which you need to sum. Consequently Index/Match or Lookups will not work since they essentially find a single value somewhere.

I agree that you can't use a PT with your current layout, which is why I suggested that you need to change to the layout I mentioned. Without this you will need either some VBA macros or possibly some very complex horrid array formulae.

Believe me, I've got too many T shirts to think otherwise, turning the layout around and using a PT is the most efficient way forward.

Hi Richard:

My original data was a two line input data to ensure that I do not complicate my needs to my peers. The actual input data would be about 600 rows tall, which will be summarized in 160 rows in the summary sheet as per the attached spreadsheet for the WTD, MTD QTD and YTD.

Could you please please provide me with Excel or VBA solution to calculate my date specific needs for the WTD, MTD, QTD and YTD sum for the 2 reporting years

Thank you for your suggestion regarding PT, but it does not work in this case especially since my needs have 160 items to be included in the report of which first 2 columns for the current date and previous days numbers are cherry picked (using Index/Match function) and the rest (WTD, MTD, QTD, YTD) is sum of values for the respective dates. And as I said in my previous post that I am not able to get the sum of values between two dates

Thanks again

Originally Posted by jubiesxl

Hi Richard:


Thank you for your suggestion regarding PT, but it does not work in this case especially since my needs have 160 items to be included in the report of which first 2 columns for the current date and previous days numbers are cherry picked (using Index/Match function) and the rest (WTD, MTD, QTD, YTD) is sum of values for the respective dates. And as I said in my previous post that I am not able to get the sum of values between two dates

Thanks again

Forget the need to summarise 600+ rows into 160 rows. Whether you have 600,000 rows or 60 it doesn't matter to a pivot table.

Are you saying that it is impossible to change your Input sheet into a layout which is compatible with a pivot table? I ask since Index Match functions (where there are many rows for the same Mill) will not cut it. They merely return a single value. My original post was made under the impression that you were looking to find a single row on the input sheet for each mill. As a minimum you will need some array formulae with specific criteria and you may well find the calculating time really slows down.

Believe me, if the layout of your data is changed you could create a pivot table in a matter of seconds and not need to worry about formulae.

VBA would be the proverbial sledgehammer to crack the proverbial nut.

Originally Posted by jubiesxl

Thank you Richard for your help and advice.

However, I do believe I need to put the daily numbers and the summing values (of wtd, mtd, etc) within the same report which is why PT is not advisable in this case.


Thanks again

..that's the difficulty I'm having in understanding why you believe that. Largely because it isn't correct. That's precisely what a PT does.

If I could understand why you believe that, and I mean believe that and show me specific examples why a PT wouldn't work rather than just a simple statement that you "believe", then I can maybe answer your fears.

Hi,

See attached.

Even ignoring a Pivot Table the data needs to be in the format I show on the data sheet in order to facilitate the use of the SUMIFS() formulae you see in the summary table in K1:Q12

Change the Report date in the yellow M3 cell on the Data tab.

Hi,

See attached.

Even ignoring a Pivot Table the data needs to be in the format I show on the data sheet in order to facilitate the use of the SUMIFS() formulae you see in the summary table in K1:Q12

Change the Report date in the yellow M3 cell on the Data tab.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.