How to sum value based on the set of specific month & year?

[Sochen]

Hi All,

I would like to make the dynamic sum value based on the set of specific month and year as to see the comparison.

test1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1						Year	2022	Apr
2							2021
3
4
5	Year	Month	Total			Year	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total
6	2021	Jan	100			2022	90	150	120	90									450
7	2021	Feb	50			2021	100	50	80	80									310
8	2021	Mar	80
9	2021	Apr	80
10	2021	May	100				Change year or month to see comparison of the specific period
11	2021	Jun	90		Question 1:		Return blank because the sum only from Jan to Apr
12	2021	Jul	110
13	2021	Aug	100
14	2021	Sep	120
15	2021	Oct	70
16	2021	Nov	150		Question 2:	Rolling down/up month based the setting month
17	2021	Dec	200
18	2022	Jan	90		Set month:	Jun
19	2022	Feb	150
20	2022	Mar	120		Normal Date	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
21	2022	Apr	90		After Set month	Jan	Feb	Mar	Apr	May	Jun
22	2022	May	30
23	2022	Jun	80
24	2022	Jul	100				Result after set month
25	2022	Aug	90				Return blank as it shows only from Jan to Jun base on the set month
26	2022	Sep	110
27	2022	Oct	30
28	2022	Nov	90
29	2022	Dec	100
30	2023	Jan	50
31	2023	Feb	400
32	2023	Mar	300
33	2023	Apr	250
34
35
Sheet1 (2)

Cell Formulas
Range	Formula
G2	G2	=G1-1
F6:F7	F6	=G1
G6:J6	G6	=SUMIFS($C$6:$C$33,$A$6:$A$33,$F6,$B$6:$B$33,G$5)
G7:J7	G7	=IF(G6=0,"",SUMIFS($C$6:$C$29,$A$6:$A$29,$F7,$B$6:$B$29,G$5))
S6:S7	S6	=SUM(G6:R6)

 

[awoohaw]

How does this work for you? I did not update conditional formating.

Mr Excel Questions2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1						Year	2022	Apr
2							2021
3
4	Year	Month	Total			Year	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total
5	2021	Jan	100			2022	90	150	120	90									450
6	2021	Feb	50			2021	100	50	80	80									310
7	2021	Mar	80
8	2021	Apr	80
9	2021	May	100				Change year or month to see comparison of the specific period
10	2021	Jun	90				Return blank because the sum only from Jan to Apr
11	2021	Jul	110
12	2021	Aug	100
13	2021	Sep	120
14	2021	Oct	70
15	2021	Nov	150
16	2021	Dec	200
17	2022	Jan	90
18	2022	Feb	150
19	2022	Mar	120
20	2022	Apr	90
21	2022	May	30
22	2022	Jun	80
23	2022	Jul	100
24	2022	Aug	90
25	2022	Sep	110
26	2022	Oct	30
27	2022	Nov	90
28	2022	Dec	100
29	2023	Jan	50
30	2023	Feb	400
31	2023	Mar	300
32	2023	Apr	250
33
Sheet11

Cell Formulas
Range	Formula
G2	G2	=G1-1
F5:F6	F5	=G1
G5:M5	G5	=IF(COLUMNS($G$5:G5)>MATCH($H$1,$B$5:$B$16,0),"",SUM((--($F$5=$A$5:$A$32))*(--(G$4=$B$5:$B$32)*($C$5:$C$32))))
G6:M6	G6	=IF(COLUMNS($G$5:G6)>MATCH($H$1,$B$5:$B$16,0),"",SUM((--($F$6=$A$5:$A$32))*(--(G$4=$B$5:$B$32)*($C$5:$C$32))))
S5:S6	S5	=SUM(G5:R5)

 

[Sochen]

How does this work for you? I did not update conditional formating.

Mr Excel Questions2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1						Year	2022	Apr
2							2021
3
4	Year	Month	Total			Year	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total
5	2021	Jan	100			2022	90	150	120	90									450
6	2021	Feb	50			2021	100	50	80	80									310
7	2021	Mar	80
8	2021	Apr	80
9	2021	May	100				Change year or month to see comparison of the specific period
10	2021	Jun	90				Return blank because the sum only from Jan to Apr
11	2021	Jul	110
12	2021	Aug	100
13	2021	Sep	120
14	2021	Oct	70
15	2021	Nov	150
16	2021	Dec	200
17	2022	Jan	90
18	2022	Feb	150
19	2022	Mar	120
20	2022	Apr	90
21	2022	May	30
22	2022	Jun	80
23	2022	Jul	100
24	2022	Aug	90
25	2022	Sep	110
26	2022	Oct	30
27	2022	Nov	90
28	2022	Dec	100
29	2023	Jan	50
30	2023	Feb	400
31	2023	Mar	300
32	2023	Apr	250
33
Sheet11

Cell Formulas
Range	Formula
G2	G2	=G1-1
F5:F6	F5	=G1
G5:M5	G5	=IF(COLUMNS($G$5:G5)>MATCH($H$1,$B$5:$B$16,0),"",SUM((--($F$5=$A$5:$A$32))*(--(G$4=$B$5:$B$32)*($C$5:$C$32))))
G6:M6	G6	=IF(COLUMNS($G$5:G6)>MATCH($H$1,$B$5:$B$16,0),"",SUM((--($F$6=$A$5:$A$32))*(--(G$4=$B$5:$B$32)*($C$5:$C$32))))
S5:S6	S5	=SUM(G5:R5)

Hi Sir, thank you for your response. However, when change change month to May or June, then the result of May and Jun in 2022 appear while the result in 2023 will not shown because there is no data in May and Jun 2023. So the sum data in 2022 should be shown base on the sum data in 2023. And also I have the question number 2 in the same exel, could you have a look?