Sales summary for Multiple sheets with irregular no of rows..!

[Ramirez]

Hello Anybody, I am here again with another headache. I came up with a problem in preparing the summary of a particular product(10 types of items) for all the 12 months for a particular Customer(no of customers 450+). Because the no of rows in a month is different from each other. That is one point. As I go updating the shipments/sales for a month, for a particular product, for a particular customer, the summary sheet should get updated dynamically irrespective of the no of rows in a sheet and at the end of the year or whenever my boss asks the status, I should get the exact no of sales figures for a particular party... eg: Rock Drillers--XYZ&Co--For all months in a year. I tried using "AUTOSUM","SUMIFS","COUNTIFS", "VLOOKUP", but the problem is with the irregular no of rows in each sheet. Please help me on this while I try on my end as this is a precedent to some other key financial calculations sheet. Here is the link for the Replica sheet. https://hotfile.com/dl/162650550/fae0f61/Shippings.xlsx.html.


Please note: No Pivot Tables, No Macros or VBA code, Only formulas. Sorry to be too strict, but that's the situation I am in. Thank you.

 

[Hui]

Carlos


This is a good example of why you shouldn't segregate your data into any time period

It make reporting a pain in the ...


I would suggest that if this is last years data copying all the data onto one sheet, month after month, with only 1 heading Row at the top

You will get 5828 rows


Then you can use a formula on the Summary Page like:


Summary!C2:


To count

=SUMPRODUCT((Total!$C$2:$C$5828=$B2)*(Total!$D$2:$D$5828=C$1))


To Add up the Cost Field (Column K)

=SUMPRODUCT((Total!$C$2:$C$5828=$B2)*(Total!$D$2:$D$5828=C$1),(Total!$K$2:$K$5828))


Copy across and down


etc

 

[Ramirez]

Thank you for the promptness, I will test it on my end for my suitability because Customer base keeps growing up every month and we need some dynamism in the file. I will let you know, if I come across a glitch. Thank you, Hui.

 

[Ramirez]

Hui, This is fine. But how should I plan for the coming years. 1) I will add a sheet every month, 2) I keep on adding new Customers. These two are inevitable. And at any time, I should know the sales figures with out any error and time wasting. How can I capture everything in the summary sheet on a real time basis? Is there any method for dynamic running total. This is a key file where at least 4/5 departments depend upon this. Obviously it will be prepared by me. Any suggestions?? Thank you.

 

[Hui]

Carlos


In General you are better to have all your data in 1 source.

This makes report writing much easier than trying to consolidate multiple sources.


In your case if you are doing regular reports which compare month to month or YTD data then having the yearly data in 1 Worksheet is recommended


6,000'ish rows per year isn't too bad for 1 worksheet


If you are required to do reports across years, you can always either

Put all the data in 1 Worksheet, if you will do those style of reports regularly, or

Leave in separate worksheets or files if you only do them infrequently and then combine them as required.


Adding new customers, etc is made easier by using 1 data source


You can also use Dynamic Formulas which extend and select teh data automatically as you add new data.


Having 1 source enables you to have a multitude of reports feeding from it including the YTD and any other reports already setup on other worksheet

 

[Ramirez]

Thanks for your suggestion. I am trying to do what you have suggested me suitably.