Summarizing data with reference to different criteria

Dear Experts,

I have attached here work sheet for your reference. In this work sheet I need the following;

01). "Monthly and Factory Wise Summary"

In this sheet, I need to calculate the total values with reference to "Month" and "Factory". For example for the month of "March", the total value for " ACCL" is 266212(cell D10). The same way for the E10,F10, G10 and I10 and across down.
Month is calculated taking "Monday to the Sunday".

02). "Weekly and Factory Wise Summary"

In this sheet, I need to calculate the total values with reference to "Month", "Week" and "Factory". For example for the first week of the month of "April", the total value for " ACCL" is 88842(cell G8). The same way it should work for the H8,I8,J8 and K8 and across down
Week is calculated taking "Monday to next Sunday".

03) "Week and Department Wise Summary"
In this sheet, I need to calculate the total values with reference to "Month", "Week" and "Department". For example for the first week of the month of "April", the total value for " BT Boys is 156026 (cell G8). The same way it should work for the other cells(departments)
Week is calculated taking "Monday to next Sunday".


The raw data is available in the sheet " Master Sheet"

Please consider that the data in this attachment have been entered manually with support of filtering. Therefore, there can be errors in case you want to verify.

Thanks in advance.
Anuruddha

Originally Posted by JeteMc

Taking this one question at a time, the proposed solution to the "Monthly and Factory Wise Summary" is as follows:
1) Take advantage of the layout of the 'Master Sheet' by adding a helper column with the month number. The first cell is manually filled in and the rest of the column is populated by the formula:

Formula:

Please Login or Register  to view this content.

2) A second helper column coverts the month numbers to dates (formatted mmmm'yy) using the DATE function as follows: =DATE(2017,Y8,1)
3) The formula that populates the 'Month and Factory Wise Summary' sheet in the range D10:H28 is:

Formula:

Please Login or Register  to view this content.

Note: Column B is now populated with dates formatted mmmm'yy
Note: This is being returned as an .xlsx file as your profile shows you have the 2016 version.
I'll work on portions 2 and 3 later unless another contributor has solved those beforehand.
Let us know if you have any questions.

Hi Jete,
Thanks a lot for your brilliant solution.
Kindly let me know which column have you target for date. I mean, is it column "O"(Cut off date) or "P"(Delivery Date).
It is my mistake not mentioning which column to be considered as the "target date" calculations.
However, if you have already considered col "O", it is right.
Pls advise.

Further, I have both excel 2016 and 2013 in my two computers. Kindly advise which version of excel is faster and error free. Also, will this solution work in both the versions without any issue?

Also let me know whether month quantity is calculated taking "first Monday to Sunday"?

Helper in "Master Data" column AB

=WEEKNUM(O8)&YEAR(O8)


helper in "Week and Factory Wise Summary" column O

=IF(D8<>"",WEEKNUM(D8)&YEAR(D8),"")


helper in "Week and Departmenty Wise Summary" column Q

=IF(D8<>"",WEEKNUM(D8)&YEAR(D8),"")

in "Week and Factory Wise Summary"

in G8

=SUMIFS('Master Sheet'!$N$8:$N$2000,'Master Sheet'!$Q$8:$Q$2000,G$6,'Master Sheet'!$AB$8:$AB$2000,$O8)

Copy across and down for April

Copy/paste for other months


in "Week and Department Wise Summary"

in F8

=SUMIFS('Master Sheet'!$N$8:$N$2000,'Master Sheet'!$B$8:$B$2000,F$6,'Master Sheet'!$AB$8:$AB$2000,$Q8)

Copy across and down for April

Copy/paste for other months

Hiiii John,
Nice to see you again. Thanks for your brilliant solution as always.
I will check this and come back to you if I find anything to be explained.

Thanks again.

Originally Posted by JohnTopley

Helper in "Master Data" column AB

=WEEKNUM(O8)&YEAR(O8)


helper in "Week and Factory Wise Summary" column O

=IF(D8<>"",WEEKNUM(D8)&YEAR(D8),"")


helper in "Week and Departmenty Wise Summary" column Q

=IF(D8<>"",WEEKNUM(D8)&YEAR(D8),"")

in "Week and Factory Wise Summary"

in G8

=SUMIFS('Master Sheet'!$N$8:$N$2000,'Master Sheet'!$Q$8:$Q$2000,G$6,'Master Sheet'!$AB$8:$AB$2000,$O8)

Copy across and down for April

Copy/paste for other months


in "Week and Department Wise Summary"

in F8

=SUMIFS('Master Sheet'!$N$8:$N$2000,'Master Sheet'!$B$8:$B$2000,F$6,'Master Sheet'!$AB$8:$AB$2000,$Q8)

Copy across and down for April

Copy/paste for other months

Hi John,
I am not understanding how the the following formula works.

=WEEKDAY(O19,1)

Because, I have attached in the forum half of the "master sheet"(because, the full sheet is around 1.3MB, which I cannot post on the forum), and now I started copying all the formulas in to my "original sheet". However, now I am getting different figures, and not matching with your work out.

Any advice pls..?

Originally Posted by JeteMc

Regarding the questions in post #3:
I bypassed both columns O and P and used column H. Whomever is entering information in the 'Master Sheet' is already deciding where the month starts and ends and putting the term 'Total quantity' in column H at that point.
I don't have either the 2013 or 2016 versions, so I don't know which is better. The solution should work with either version as it was produced using the 2010 version.
Let us know if you have any questions.

Column "H" has "name(style name)..?? What is the relation with that?

the WEEKDAY column is redundant.

Originally Posted by JohnTopley

the WEEKDAY column is redundant.

Hi John,
I have re worked the original sheet using your solutions (for now monthly and factory wise summary only). I copied the formulas you have provided, but my totals are not matching with your totals. I don't see anything wrong with the formulas. I checked many times.

I have again attached the original work sheet (master sheet contains data only up to July as the I cannot post more than 1000kb on the forum). If you can provide me an e mail ID, I can send you the whole work sheet(original).

Pls check and advise me about my mistakes.

Note: I have added some extra information on this work sheet for the "Monthly and factory wise summary". However, I have used the same formulas you have provided.

Regards
Anuruddha

Originally Posted by JohnTopley

The formula is not mine (it's JeteMc's) BUT you do not check the formulae very well .....

=SUMIFS('Master Sheet'!$N$7:$N$1090,'Master Sheet'!$AB$7:$AB$1090,$B16,'Master Sheet'!$R$7:$R$1090,E$5)

perhaps should be .....


=SUMIFS('Master Sheet'!$O$7:$O$1090,'Master Sheet'!$AB$7:$AB$1090,$B16,'Master Sheet'!$R$7:$R$1090,E$5)


If it should be O not N then the formulae I gave need adjusting also as I summed column N

You did not specify which column(s) to use so an assumption was made that it was N and at no time did you correct this.

Yes. I got it. I have missed the main part of the formula to check. Stupid me...

Originally Posted by JeteMc

Regarding the question; Column "H" has "name(style name)..?? What is the relation with that?: The relation is that on the Master Sheet in H95 is the text "March'17 Total quantity" and in H354 is the text "April'17 Total quantity" and so on. So whoever is putting together the master sheet is already determining where one month ends and another begins.
Let us know if you have any questions.

Okay. Thanks for taking time to explain, JeteMc.

The attached has updated formulae to reflect the additional columns.

Originally Posted by JohnTopley

The attached has updated formulae to reflect the additional columns.

Thanks a lot John. Feel so confident when you are on the forum. Thanks for your support as always.