Sum/Avg a Quarter from a full monthly Year

[shepcs]

Appreciate any help on how to elegant sum for a quarter given the date (or specified quarter number). I can create a switch simply by the quarter number (just hardcoded 1s below) but there might be an easier way? I would need to also average it. Really appreciate any pointers!

MrExcel.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Report Month	7
2	Report Quarter	3
3	Report Year	2024
4	End of Month	07/31/24
5
6									1	1	1
7			Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	2024
8															TOTAL
9
10	ENERGY MARGIN
11	600100	REVENUE - ENERGY SALES	-	-	-	-	-	-	-	-	-	-	-	-	-
12	600105	REVENUE - ENERGY - REAL TIME	-	-	-	-	-	-	-	-	-	-	-	-	-
13	600110	REVENUE - ENERGY - DAY AHEAD	483,987	751,160	709,401	828,741	585,281	1,095,814	2,259,505	2,102,308	1,135,976	681,805	1,064,370	681,291	12,379,639
14
15
16
17	Q3
Sheet1

Cell Formulas
Range	Formula
D2	D2	=ROUNDUP(D1/3,0)
D4	D4	=EOMONTH(DATE(D3,D1,1),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4	Expression	=#REF!<>0	text	YES

 

[myall_blues]

Like this perhaps?

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Report Month	7
2	Report Quarter	3
3	Report Year	2024
4	End of Month	45504
5
6
7			31/01/2024	29/02/2024	31/03/2024	30/04/2024	31/05/2024	30/06/2024	31/07/2024	31/08/2024	30/09/2024	31/10/2024	30/11/2024	31/12/2024	2024
8															TOTAL
9
10	ENERGY MARGIN
11	600100	REVENUE - ENERGY SALES	0	0	0	0	0	0	0	0	0	0	0	0	0
12	600105	REVENUE - ENERGY - REAL TIME	0	0	0	0	0	0	0	0	0	0	0	0	0
13	600110	REVENUE - ENERGY - DAY AHEAD	483987.4633	751159.7176	709401.4219	828741.3224	585281.2941	1095813.62	2259504.727	2102307.578	1135975.803	681804.9903	1064369.951	681291.0846	1E+07
14
15
16		Sum	Average
17	Q3	1944548.6	216060.96
Sheet1

Cell Formulas
Range	Formula
D2	D2	=ROUNDUP(D1/3,0)
D4	D4	=EOMONTH(DATE(D3,D1,1),0)
D17	D17	=SUM(FILTER($E$11:$P$13,CHOOSE(MONTH($E$7:$P$7),3,3,3,4,4,4,1,1,1,2,2,2)=$D$2))
E17	E17	=AVERAGE(FILTER($E$11:$P$13,CHOOSE(MONTH($E$7:$P$7),3,3,3,4,4,4,1,1,1,2,2,2)=$D$2))

 

[shepcs]

Thanks! Sorry for the delay. That's not summing it or averaging it correctly? I'm trying to play with it and make it row specific by column C. 6001110 for example.

 

[myall_blues]

It is summing correctly - make sure you ask for what you want, preferably with an example of desired results.

Try this.

Book3
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Report Month	7
2		Report Quarter	3
3		Report Year	2024
4		End of Month	31/07/2024
5
6
7				31/01/2024	29/02/2024	31/03/2024	30/04/2024	31/05/2024	30/06/2024	31/07/2024	31/08/2024	30/09/2024	31/10/2024	30/11/2024	31/12/2024	2024
8																TOTAL
9
10		ENERGY MARGIN
11		600100	REVENUE - ENERGY SALES	5	0	0	0	0	0	0	0	0	0	0	0	0
12		600105	REVENUE - ENERGY - REAL TIME	0	0	0	0	0	0	0	0	0	0	0	0	0
13		600110	REVENUE - ENERGY - DAY AHEAD	483987.4633	751159.7176	709401.4219	828741.3224	585281.2941	1095813.62	2259504.727	2102307.578	1135975.803	681804.9903	1064369.951	681291.0846	1E+07
14
15
16			Sum	Average
17	Q3	600100	5	1.666666667
18		600105	0	0
19		600110	1944548.603	648182.8676
Sheet1

Cell Formulas
Range	Formula
D2	D2	=ROUNDUP(D1/3,0)
D4	D4	=EOMONTH(DATE(D3,D1,1),0)
D17:D19	D17	=SUM(FILTER(FILTER($E$11:$P$13,(CHOOSE(MONTH($E$7:$P$7),3,3,3,4,4,4,1,1,1,2,2,2)=$D$2)),$C$11:$C$13=$C17))
E17:E19	E17	=AVERAGE(FILTER(FILTER($E$11:$P$13,(CHOOSE(MONTH($E$7:$P$7),3,3,3,4,4,4,1,1,1,2,2,2)=$D$2)),$C$11:$C$13=$C17))