Sum total of daily values for month

[Marklarbear]

Hi brains trust

Below is a sample set of the spreadsheet I'm working on - the original data set is by 15 minute interval for every day within a rolling 365 day date range - which is a rather large set of data (263146 cells of data).

What I would like to do is have a formula that totals the daily value for the cells in yellow (I have highlighted these cells purely for this example) - ie total for heading "L" added together to give me a monthly total.

In cell C25 & C26 is a very basic way of adding the values, however I need a more 'dynamic' way of calculating the data - as the first date of the data set (cell B3) will change every time we run this process. - ie this month we may start on Monday 22nd Aug, Next month we may start on Monday 12th Sep or Monday 26th Sep etc etc... the start date will always be a Monday but it could be any Monday in the month.

Hopefully this makes sense on what i'm trying to achieve....

Book1.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	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA
3		22/08/2022	Mon														23/08/2022	Tue														24/08/2022	Wed														25/08/2022	Thu														26/08/2022	Fri														27/08/2022	Sat														28/08/2022	Sun
4		A	B	C	D	E	F	G	H	I	J	K	L	M	N		A	B	C	D	E	F	G	H	I	J	K	L	M	N		A	B	C	D	E	F	G	H	I	J	K	L	M	N		A	B	C	D	E	F	G	H	I	J	K	L	M	N		A	B	C	D	E	F	G	H	I	J	K	L	M	N		A	B	C	D	E	F	G	H	I	J	K	L	M	N		A	B	C	D	E	F	G	H	I	J	K	L	M	N
5	Daily Total	0	0	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00		0	118	0.00	4.45	0.00	5.11	0.00	33.80	0.00	8.61	0.00	8.78	0.00	66.75		0	86	0.00	3.53	0.00	4.06	0.00	26.80	0.00	6.85	0.00	6.99	0.00	53.14		0	78	0.00	3.37	0.00	3.87	0.00	25.60	0.00	6.59	0.00	6.73	0.00	51.12		0	86	0.00	3.53	0.00	4.06	0.00	26.80	0.00	6.77	0.00	6.91	0.00	52.48		0	36	0.00	1.92	0.00	2.21	0.00	14.60	0.00	3.68	0.00	3.76	0.00	28.57		0	0	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
6
7
8
9
10		29/08/2022	Mon														30/08/2022	Tue														31/08/2022	Wed														1/09/2022	Thu														2/09/2022	Fri														3/09/2022	Sat														4/09/2022	Sun
11		A	B	C	D	E	F	G	H	I	J	K	L	M	N		A	B	C	D	E	F	G	H	I	J	K	L	M	N		A	B	C	D	E	F	G	H	I	J	K	L	M	N		A	B	C	D	E	F	G	H	I	J	K	L	M	N		A	B	C	D	E	F	G	H	I	J	K	L	M	N		A	B	C	D	E	F	G	H	I	J	K	L	M	N		A	B	C	D	E	F	G	H	I	J	K	L	M	N
12	Daily Total	0	92	0.00	3.68	0.00	4.24	0.00	28.00	0.00	7.03	0.00	7.18	0.00	54.55		0	87	0.00	3.63	0.00	4.18	0.00	27.60	0.00	7.02	0.00	7.17	0.00	54.46		0	80	0.00	3.37	0.00	3.87	0.00	25.60	0.00	6.57	0.00	6.70	0.00	50.96		0	78	0.00	3.37	0.00	3.87	0.00	25.60	0.00	6.59	0.00	6.73	0.00	51.12		0	86	0.00	3.53	0.00	4.06	0.00	26.80	0.00	6.77	0.00	6.91	0.00	52.48		0	36	0.00	1.92	0.00	2.21	0.00	14.60	0.00	3.68	0.00	3.76	0.00	28.57		0	0	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
13
14
15
16
17		5/09/2022	Mon														6/09/2022	Tue														7/09/2022	Wed														8/09/2022	Thu														9/09/2022	Fri														10/09/2022	Sat														11/09/2022	Sun
18		A	B	C	D	E	F	G	H	I	J	K	L	M	N		A	B	C	D	E	F	G	H	I	J	K	L	M	N		A	B	C	D	E	F	G	H	I	J	K	L	M	N		A	B	C	D	E	F	G	H	I	J	K	L	M	N		A	B	C	D	E	F	G	H	I	J	K	L	M	N		A	B	C	D	E	F	G	H	I	J	K	L	M	N		A	B	C	D	E	F	G	H	I	J	K	L	M	N
19	Daily Total	0	92	0.00	3.68	0.00	4.24	0.00	28.00	0.00	7.03	0.00	7.18	0.00	54.55		0	87	0.00	3.63	0.00	4.18	0.00	27.60	0.00	7.02	0.00	7.17	0.00	54.46		0	80	0.00	3.37	0.00	3.87	0.00	25.60	0.00	6.57	0.00	6.70	0.00	50.96		0	78	0.00	3.37	0.00	3.87	0.00	25.60	0.00	6.59	0.00	6.73	0.00	51.12		0	86	0.00	3.53	0.00	4.06	0.00	26.80	0.00	6.77	0.00	6.91	0.00	52.48		0	36	0.00	1.92	0.00	2.21	0.00	14.60	0.00	3.68	0.00	3.76	0.00	28.57		0	0	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
20
21
22
23
24		Month	L Total
25		Aug	54.21
26		Sep	55.83
27		Oct	?
28		Nov	?
29		Dec	?
Sheet1

Cell Formulas
Range	Formula
C3,CO17,BZ17,BK17,AV17,AG17,R17,C17,CO10,BZ10,BK10,AV10,AG10,R10,C10,CO3,BZ3,BK3,AV3,AG3,R3	C3	=TEXT(B3,"ddd")
Q3,CN17,BY17,BJ17,AU17,AF17,Q17,CN10,BY10,BJ10,AU10,AF10,Q10,CN3,BY3,BJ3,AU3,AF3	Q3	=B3+1
B10,B17	B10	=B3+7
C25	C25	=M5+AB5+AQ5+BF5+BU5+CJ5+CY5+M12+AB12+AQ12
C26	C26	=BF12+BU12+CJ12+CY12+M19+AB19+AQ19+BF19++BU19+CJ19+CY19
B25	B25	=B3
B26:B29	B26	=DATE(YEAR(B25),MONTH(B25)+1,1)

 

[SanjayGMusafir]

I have understood your requirement to some extent. But just to help you better, I need to understand few things.

Why you have kept so many blank rows and columns between all dates and repeating A-N for every date. I have seen formulae populated just with one date change every other date shall change. But for my understanding can't all these be stacked together?

That gives you far more control over data and ability to play with data knowing you are using Excel 365.

 

[Marklarbear]

I have understood your requirement to some extent. But just to help you better, I need to understand few things.

Why you have kept so many blank rows and columns between all dates and repeating A-N for every date. I have seen formulae populated just with one date change every other date shall change. But for my understanding can't all these be stacked together?

That gives you far more control over data and ability to play with data knowing you are using Excel 365.

This is a spreadsheet that i've inherited.... The columns A-N are various metrics that get reported on - all I have done is replaced the metric heading with a generic value to keep my boss happy when posting this question. For the days that have a 0 value for all metrics - this indicates the line was not open on that particular day.

Each day has 96 rows of data (each row represents a 15 minute interval starting from the 12:00am interval to the 11:45pm interval). This repeats down the page for 53 weeks. In the example above, i'm only showing the summary row for each days total data.

In my original doc, the first summary row is row 5 & the last summary row is row 5205.... which is way too large to upload here. I was hoping a guru would be able to work on the sample data I uploaded and then I would edit the formula to cover my original data range (in my original doc it is A5:DA5300)

Hope this helps

 

[Peter_SSs]

Each day has 96 rows of data (each row represents a 15 minute interval starting from the 12:00am interval to the 11:45pm interval). This repeats down the page for 53 weeks. In the example above, i'm only showing the summary row for each days total data.

So it seems that we are not testing on a representative sample. Therefore, although this produces the results that you are expecting for that sample data, I'm guessing that it is not going to work with your real data.

However, for the sample data posted, try this in cell C25 and copy down.

=LET(f,FILTER(B$3:DA$19,(B$4:DA$4="A")+(B$4:DA$4="L")),rws,SEQUENCE(ROWS(f)-2),cols,SEQUENCE(,COLUMNS(f)/2,,2),SUMPRODUCT(--(TEXT(INDEX(f,rws,cols),"mmyy")=TEXT(B25,"mmyy")),INDEX(f,rws+2,cols+1)))