Sum cells dynamically based on Date

[anonemous]

I have a worksheet where each month, a new column is inserted to the front with the past month's worth of data.
There are two separate columns to output Year to Date sums along with the change in percentage, which would be Jan-March 2020 compared with Jan-March 2019 and the change. (highlighted in red below)

B2 = SUM(E2:G2)
C2 = SUM(Q2:S2)
D2 = (c2-b2)/b2

How can I modify these columns so that they will do it automatically each month instead of having to revise my formula each month?
I've found that sometimes I might make mistakes and not realize the YTD is summing the wrong month.
Previously I've found that I can use INDIRECT to keep cells static, but this doesn't work when each month is dynamic in a YTD format.

Advice always appreciated from here!

 

[CA_Punit]

Please post the Excel Sheet.
Please let me know if new month is updated will be updated in "E" colomn

 

[anonemous]

Posted below, as mentioned each month there will be a new month in the E column..
e.x.:
At the end of this month, E will be "April 2020". Column B, C, D will be (sum of jan-april 2020)-(sum of jan-april 2019)/(sum of jan-april 2019)
End of May, E will be "May 2020", Column B, C, D will be (sum of jan-may 2020)-(sum of jan-may 2019)/(sum of jan-may 2019)

sheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Data	YTD TTL	pYTD TTL	PCT %	Mar-20	Feb-20	Jan-20	Dec	Nov	Oct	Sep	Aug	Jul	Jun	May	Apr	Mar	Feb	Jan-19
2	Data 1	6	42	600%	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
3	Data 2	9	18	100%	3	3	3	4	5	6	7	8	9	10	9	8	7	6	5
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=SUM(E2:G2)
C2:C3	C2	=SUM(Q2:S2)
D2:D3	D2	=IFERROR(((C2-B2)/B2),"-%")

 

[Peter_SSs]

Give this a try

20 04 10.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Data	YTD TTL	pYTD TTL	PCT %	Mar-20	Feb-20	Jan-20	Dec-19	Nov-19	Oct-19	Sep-19	Aug-19	Jul-19	Jun-19	May-19	Apr-19	Mar-19	Feb-19	Jan-19
2	Data 1	6	42	600%	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
3	Data 2	9	18	100%	3	3	3	4	5	6	7	8	9	10	9	8	7	6	5
YTD

Cell Formulas
Range	Formula
B2:B3	B2	=SUM(OFFSET(D2,,1,,MONTH(OFFSET(D$1,,1))))
C2:C3	C2	=SUM(OFFSET(D2,,13,,MONTH(OFFSET(D$1,,1))))
D2:D3	D2	=IFERROR(((C2-B2)/B2),"-%")

 

[Peter_SSs]

The suggestions in my previous post use the volatile function OFFSET. If that has an impact on your sheet's performance, here is a non-volatile way to get the sums.

20 04 10.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Data	YTD TTL	pYTD TTL	PCT %	Mar-20	Feb-20	Jan-20	Dec-19	Nov-19	Oct-19	Sep-19	Aug-19	Jul-19	Jun-19	May-19	Apr-19	Mar-19	Feb-19	Jan-19
2	Data 1	6	42	600%	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
3	Data 2	9	18	100%	3	3	3	4	5	6	7	8	9	10	9	8	7	6	5
YTD (2)

Cell Formulas
Range	Formula
B2:B3	B2	=SUM(INDEX(D2:S2,2):INDEX(D2:S2,1+MONTH(INDEX(D$1:S$1,2))))
C2:C3	C2	=SUM(INDEX(D2:S2,14):S2)
D2:D3	D2	=IFERROR(((C2-B2)/B2),"-%")

 

[anonemous]

Cheers, thanks Peter! Offset formula and the alternative one have both worked. Already using it and grinning from saving time. Thanks again !

 

[Peter_SSs]

You're welcome. Glad to help. Thanks for the follow-up.

 

[anderma8]

I'm trying to solve a similar issue but my months are in order: 1/1/2020 | 2/1/2020 | 3/1/2020 | .... YTD 2020

I've tried a few things but nothing successful.... how would I be able to leverage the above?

THANKS!
Mark

 

[Peter_SSs]

how would I be able to leverage the above?

Can you post a small set of sample data and expected results (manually entered) with XL2BB so that we can be sure of your layout, data and requirements?

Also, please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[anderma8]

I've updated my environment (sorry about that). I can't seem to add the add-in as this is a company pc and they lock it down pretty well so I've pasted the information below.

Location

Jan-19

Feb-19

Mar-19

Apr-19

May-19

Jun-19

Jul-19

Aug-19

Sep-19

Oct-19

Nov-19

Dec-19

2019 Total

Jan-20

Feb-20

Mar-20

Apr-20

May-20

Jun-20

Location 1

46,049​

43,748​

44,348​

42,880​

39,920​

36,567​

35,545​

42,483​

35,709​

38,270​

31,956​

28,725​

466,200

42,070

32,563

31,683​

Location 2

11,647​

10,511​

12,659​

13,599​

12,521​

60,937

Location 3

56,005​

55,037​

67,210​

48,630​

65,952​

51,203​

55,902​

47,710​

49,093​

50,496​

52,537​

25,190​

624,965

51,556

49,796

55,623​

Location 4

55,596​

50,597​

57,131​

52,710​

53,237​

59,279​

57,239​

51,856​

50,203​

58,444​

54,478​

13,473​

614,243

12,363

54,539

52,832​

Location 5

13,014​

13,187​

11,846​

13,481​

14,766​

13,506​

13,626​

13,420​

13,374​

13,879​

13,410​

11,232​

158,741

13,077

11,036

13,998​

Location 6

37,082​

35,820​

39,178​

28,416​

34,402​

25,363​

36,662​

40,073​

37,379​

43,015​

35,491​

26,095​

418,976

35,764

29,549

33,102​

The expected outcome based on a date update:

3/1/2020​	Current Year YTD	Prior Year
Location 1	106,316	134,145​
Location 2	-	34,817​
Location3	156,975	178,252​
Location 4	119,734	163,324​
Location 5	38,111	38,047​
Location 6	98,415	112,080​

Sorry It's not in the greatest format!
Mark