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!

 

[Peter_SSs]

I've updated my environment

Thanks for that.

Try these, copied down.

anderma8 2020-04-22 1.xlsm
	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
1	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	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20
2	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
3	Location 2	11,647	10,511	12,659	13,599	12,521								60,937
4	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
5	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
6	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
7	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
8
9
10	1/03/2020	Current YTD	Prior Year
11	Location 1	106,316	134,145
12	Location 2	0	34,817
13	Location 3	156,975	178,252
14	Location 4	119,734	163,324
15	Location 5	38,111	38,047
16	Location 6	98,415	112,080
YTD (4)

Cell Formulas
Range	Formula
B11:B16	B11	=SUMPRODUCT(O$2:Z$7,(MONTH(O$1:Z$1)<=MONTH(A$10))*(A$2:A$7=A11))
C11:C16	C11	=SUMPRODUCT(B$2:M$7,(MONTH(B$1:M$1)<=MONTH(A$10))*(A$2:A$7=A11))

 

[anderma8]

I have to say - THANK YOU!!!!!! This is going to make my life SOOOO much easier! I never worked with Sumproduct before... I'll definitely be using this!

 

[Peter_SSs]

I have to say - THANK YOU!!!!!! This is going to make my life SOOOO much easier! I never worked with Sumproduct before... I'll definitely be using this!

You're welcome.