Sum Sales Values by Dates over different periods

[Zakky]

Hello, I have a few challenges that I need your help with. I have a daily sales log which i have to convert to provide data in three different formats:
1) weekly - sum 7 consecutive days from a starting date;
2) weekly - starting 1st of each month and up to including Sunday of that week. The working week is from Monday to Sunday, but if 1st of the month is, say, Friday then it has to start from Friday and up to including week ending Sunday, so 3 days trading for that week. So week ending dates from the 1st will be displayed as header.
3) monthly - starting from the 1st and up to including last day of the month. Regardless on what day the month ends, it has to be up the last day of the month.

The cells in blue are currently all manual SUM values. I t would be great if there were dynamic functions that could do the heavy lifting as and all the dates should be driven from data in row 2 (Daily Units Sold).

Thank you!

 

[RobP]

apologies I've run out of time today and need to leave the office, but heres a solution for your first 7 days ..

Perhaps others might join in now the data is here for them to easily use : ignore formula in C2, that was for me to get your dates in.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	Daily Units Sold
2	Product	01-Jan-18	02-Jan-18	03-Jan-18	04-Jan-18	05-Jan-18	06-Jan-18	07-Jan-18	08-Jan-18	09-Jan-18	10-Jan-18	11-Jan-18	12-Jan-18	13-Jan-18	14-Jan-18	15-Jan-18	16-Jan-18	17-Jan-18	18-Jan-18	19-Jan-18	20-Jan-18	21-Jan-18	22-Jan-18	23-Jan-18	24-Jan-18	25-Jan-18	26-Jan-18
3		Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fn	Sat	Sun	Mon	Tue	Wed	Thu	Fn	Sat
4	A	150	180	216	259	311	373	448	537	300	360	432	295	355	426	511	285	342	410	281	337	404	485	249	299	358	430	516
5
6	Weekly
7	A	07-Jan-18	14-Jan-18	21-Jan-18	28-Jan-18	04-Feb-18
8		1937	2705	2570	1821
9
10	Weekly (from the 1st of each month up to and including Sunday of that week. The working week is from Monday to Sunday. The last week has to be up to the last day of the month even if the last week is only. say. a 4-day week)
11	A	07-Jan-18	14-Jan-18	21-Jan-18	28-Jan-18 31-Jan-18		04-Feb-18	and so on..
12		1,937	2,705	2,570	2,956	747	1
13
14	Monthly Sales (from the 1st of each month up to and including the last day of each month.
15		Jan '18	Feb '18	and so on.
16
17
18	All the dates should be driven from data in row 2 (Daily Units Sold)
19
20
21
Sheet1

Cell Formulas
Range	Formula
C2:AA2	C2	=B2+1
B8:E8	B8	=SUM(CHOOSECOLS(TRANSPOSE(FILTER($B2:$AA4,($B2:$AA2>B7-7)*($B2:$AA2<=B7),"")),3))

 

[Zakky]

RobP, Thank you for replying. The first 7-day solution is perfect and does exactly what i want. Your help is greatly appreciated. Have a good day. I know i will, thanks to your good self.

 

[Zakky]

RobP, No one has joined in to assist. I would be grateful if you could provide solutions for the remaining two challenges. Thank you.

 

[RobP]

Hi, I think this is about as close as I can get. Weekly / start / end of month formula is a bit brutal, and requires the user to enter the correct dates in order to function. Not managed to find a way to have a formula generate the correct dates at present, but if I do then I'll let you know sometime.

Cheers
Rob

weeklysales.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1	Daily Units Sold
2	Product	01/01/2018	02/01/2018	03/01/2018	04/01/2018	05/01/2018	06/01/2018	07/01/2018	08/01/2018	09/01/2018	10/01/2018	11/01/2018	12/01/2018	13/01/2018	14/01/2018	15/01/2018	16/01/2018	17/01/2018	18/01/2018	19/01/2018	20/01/2018	21/01/2018	22/01/2018	23/01/2018	24/01/2018	25/01/2018	26/01/2018	27/01/2018	28/01/2018	29/01/2018	30/01/2018	31/01/2018	01/02/2018	02/02/2018	03/02/2018	04/02/2018
3		Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fn	Sat	Sun	Mon	Tue	Wed	Thu	Fn	Sat	Sun	Mon	Tue	Wed	Thu	Fn	Sat	Sun
4	A	150	180	216	259	311	373	448	537	300	360	432	295	355	426	511	285	342	410	281	337	404	485	249	299	358	430	516	619	485	249	299	358	430	516	516
5
6	Weekly
7		07/01/2018	14/01/2018	21/01/2018	28/01/2018	04/02/2018
8	A	1937	2705	2570
9
10	Weekly (from the 1st of each month up to and including Sunday of that week. The working week is from Monday to Sunday. The last week has to be up to the last day of the month even if the last week is only. say. a 4-day week)
11		07/01/2018	14/01/2018	21/01/2018	28/01/2018	31/01/2018	07/02/2018
12	A	1937	2705	2570	2956	1033
13
14	Monthly Sales (from the 1st of each month up to and including the last day of each month.
15		01/01/2018	01/02/2018
16	A	11201	1820
17
18	All the dates should be driven from data in row 2 (Daily Units Sold)
19
20
21
Sheet1

Cell Formulas
Range	Formula
C2:AJ2	C2	=B2+1
B8:C8	B8	=SUM(CHOOSEROWS(FILTER($B2:$AJ4,($B2:$AJ2>B7-7)*($B2:$AJ2<=B7),""),3))
D8	D8	=SUM(CHOOSEROWS(FILTER($B2:$AI4,($B2:$AI2>D7-7)*($B2:$AI2<=D7),""),3))
C11:E11,G11	C11	=+B11+7
F11	F11	=+E11+3
B12:F12	B12	=LET(firstday,"01/"&MONTH(B11)&"/"&YEAR(B11),lastday,EOMONTH(firstday,0),thisweek,IF(B11-firstday<7,FILTER($B2:$AJ4,($B2:$AJ2>=B11-(B11-firstday))*($B2:$AJ2<=B11),""),IF(lastday-B11=0,FILTER($B2:$AJ4,($B2:$AJ2>A11)*($B2:$AJ2<=B11),""),FILTER($B2:$AJ4,($B2:$AJ2>(B11-7))*($B2:$AJ2<=B11),""))),result,SUM(CHOOSEROWS(thisweek,3)),result)
B16:C16	B16	=SUMPRODUCT($B4:$AJ4,(MONTH($B2:$AJ2)=MONTH(B15))*1)

 

[Zakky]

RobP, You've done more than i expected. I am very grateful for you help, and if we were in a pub I would be buying all evening. I wish you the best. Thank you! P.S. you've saved my bacon.

 

[RobP]

Your Welcome

Thanks for your feedback … I’ll enjoy the virtual beer !

Rob