Sum dataset by month and year separately

[drluke]

I have a dataset of product sales by period and customer. Every new period data for that period is added to the dataset (i.e. the dataset has cumulative information in it).

Every period a report is prepared for each customer showing product sales for the period and the year to date. So period 2 will have a column showing period 2 sales and a column showing period 1+2 sales. Every periodical report is a new workbook.

My question is how to add together the cumulative sales without having to link to each periodical workbook. Ideally I want to use the cumulative dataset (which is in every new workbook) to do it.

Any advice much appreciated.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Per	C Numb	Blue	Green	Red	Pink	TOTAL					C Numb	1010			C Numb	1010
2	1	1010	100	526	458	1564	2648					Per	1			Per	2
3	1	1020	458	326	1125	58	1967
4	1	1030	856	995	5	5864	7720						1	YTD			2	YTD
5	1	1040	758	9995	4586	5235	20574					Blue	100	100		Blue	653	753
6	2	1010	653	333	4589	35246	40821					Green	526	526		Green	333	859
7	2	1020	458	326	1125	58	1967					Red	458	458		Red	4589	5047
8	2	1030	856	995	5	5864	7720					Pink	1564	1564		Pink	35246	36810
9	2	1040	758	9995	4586	5235	20574					TOTAL	2648	2648		TOTAL	40821	43469
10	3	1010	100	526	458	1564	2648
11	3	1020	458	326	1125	58	1967					Workbook 1				Workbook 2
12	3	1030	856	995	5	5864	7720
13	3	1040	758	9995	4586	5235	20574
14
15
16
Sheet1

Cell Formulas
Range	Formula
M5	M5	=SUMIFS($C$2:$C$13,$B$2:$B$13,$M$1,$A$2:$A$13,$M$2)
N5:N8	N5	=M5
M6	M6	=SUMIFS($D$2:$D$13,$B$2:$B$13,$M$1,$A$2:$A$13,$M$2)
M7	M7	=SUMIFS($E$2:$E$13,$B$2:$B$13,$M$1,$A$2:$A$13,$M$2)
M8	M8	=SUMIFS($F$2:$F$13,$B$2:$B$13,$M$1,$A$2:$A$13,$M$2)
M9:N9,Q9:R9	M9	=SUM(M5:M8)
Q5	Q5	=SUMIFS($C$2:$C$13,$B$2:$B$13,$Q$1,$A$2:$A$13,$Q$2)
Q6	Q6	=SUMIFS($D$2:$D$13,$B$2:$B$13,$Q$1,$A$2:$A$13,$Q$2)
Q7	Q7	=SUMIFS($E$2:$E$13,$B$2:$B$13,$Q$1,$A$2:$A$13,$Q$2)
Q8	Q8	=SUMIFS($F$2:$F$13,$B$2:$B$13,$Q$1,$A$2:$A$13,$Q$2)
G2:G13	G2	=SUM(C2:F2)

 

[RasGhul]

You could use Power Query to update the data set in each new workbook from your master data table.

e.g. update your master sheet, when you open a customer workbook & refresh it calls the updated data from the master sheet.

 

[RasGhul]

Maybe

Book1
	L	M	N	O	P	Q	R
1	C Numb	1010			C Numb	1010
2	Per	1			Per	2
3
4		1	YTD			2	YTD
5	Blue	100	100		Blue	653	753
6	Green	526	526		Green	333	859
7	Red	458	458		Red	4589	5047
8	Pink	1564	1564		Pink	35246	36810
9	TOTAL	2648	2648		TOTAL	40821	43469
10
11	Workbook 1				Workbook 2
Sheet1

Cell Formulas
Range	Formula
M5:M8	M5	=SUMPRODUCT(($B$2:$B$13=$M$1)*($A$2:$A$13=$M$2)*($C$1:$F$1=L5)*$C$2:$F$13)
N5:N9	N5	=M5
M9	M9	=SUMPRODUCT(($B$2:$B$13=$M$1)*($A$2:$A$13=$M$2)*$C$2:$F$13)
Q5:Q8	Q5	=SUMPRODUCT(($B$2:$B$13=$Q$1)*($A$2:$A$13=$Q$2)*($C$1:$F$1=P5)*$C$2:$F$13)
R5:R8	R5	=SUMPRODUCT(($B$2:$B$13=$Q$1)*($A$2:$A$13>=$Q$2)*($C$1:$F$1=P5)*$C$2:$F$13)
Q9	Q9	=SUMPRODUCT(($B$2:$B$13=$Q$1)*($A$2:$A$13=$Q$2)*$C$2:$F$13)
R9	R9	=SUMPRODUCT(($B$2:$B$13=$Q$1)*($A$2:$A$13>=$Q$2)*$C$2:$F$13)