How to calculate dynamic Running YTD Report?

[Manith]

Hi All,

I would like to ask about the follow report which can be comparing between year 2021 vs 2020 or 2020 Vs 2019 which need to use only onn table as following. So, current table below is comparing between 2021 Vs 2020, however I want to switch to 2020 Vs 2019 by changing only the year in "Red" hightlight or maybe you can create another hyperlink tape to change the year. Pls note that the current month&year is Aug-2021, so the Running YTD for 2021 will be Jan-July 2021.

Result after changing the year as running YTD also changed.

Thank you!

 

[Manith]

Result after changing the year as running YTD also changed.



Thank you!

Please note that

Hi All,

I would like to ask about the follow report which can be comparing between year 2021 vs 2020 or 2020 Vs 2019 which need to use only onn table as following. So, current table below is comparing between 2021 Vs 2020, however I want to switch to 2020 Vs 2019 by changing only the year in "Red" hightlight or maybe you can create another hyperlink tape to change the year. Pls note that the current month&year is Aug-2021, so the Running YTD for 2021 will be Jan-July 2021.



Result after changing the year as running YTD also changed.



Thank you!

Please note that Aug-2021 has sale data until today is 20, however the Running YTD for 2021 will not include the sale data in Aug-2021.
Thank you!

 

[alansidman]

Cannot manipulate data in a picture. Please use XL2BB (See my signature if unfamiliar) to upload sample data.

 

[Manith]

Cannot manipulate data in a picture. Please use XL2BB (See my signature if unfamiliar) to upload sample data.

Please kindly find the below report. And please note that The Running YTD in 2021 should be from Jan-July 2021 which in not include the Aug-2021 (In Red Hightlight). So the Running YTD in 2021 should be 170.

Running YTD.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
2						End of Last Month	31-Jul-21
3		Sales Data				2021
4		Year	Month	Sales		2020
5		2019	Jan	5
6		2019	Feb	10		Main Table
7		2019	Mar	10		Year	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total	Running YTD
8		2019	Apr	15		2021	10	20	30	20	25	30	35	20	0	0	0	0	190	190
9		2019	May	20		2020	10	20	25	25	30	25	30	40	35	45	50	60	395	395
10		2019	Jun	25		Comparison	0%	0%	20%	-20%	-17%	20%	17%	-50%	-100%	-100%	-100%	-100%	-52%	-52%
11		2019	Jul	30
12		2019	Aug	25
13		2019	Sep	30
14		2019	Oct	35
15		2019	Nov	30
16		2019	Dec	40
17		2020	Jan	10
18		2020	Feb	20
19		2020	Mar	25
20		2020	Apr	25
21		2020	May	30
22		2020	Jun	25
23		2020	Jul	30
24		2020	Aug	40
25		2020	Sep	35
26		2020	Oct	45
27		2020	Nov	50
28		2020	Dec	60
29		2021	Jan	10
30		2021	Feb	20
31		2021	Mar	30
32		2021	Apr	20
33		2021	May	25
34		2021	Jun	30
35		2021	Jul	35
36		2021	Aug	20
37
Sheet1

Cell Formulas
Range	Formula
G2	G2	=EOMONTH(TODAY(),-1)
G8:R8	G8	=SUMIFS($D$5:$D$36,$B$5:$B$36,$F$3,$C$5:$C$36,G$7)
S8:S9	S8	=SUM(G8:R8)
T8:T9	T8	=SUMIF($B$5:$B$36,F8,$D$5:$D$36)
G9:R9	G9	=SUMIFS($D$5:$D$36,$B$5:$B$36,$F$4,$C$5:$C$36,G$7)
G10:T10	G10	=G8/G9-1
F8:F9	F8	=F3