Need Rolling 12 Month Sum Across Two Sheets

[dwgnome]

Looking for solution to compute the rolling twelve month sum but when part of the data is from the current sheet and one other sheet. It is the monthly totals at D35, F35, H35 etc that are used for the rolling 12-month totals.

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M	N
1	*	Sheet 2	*	*	*	*	*	*	*	*	*	*	*
2	Day	Jan 16	*	Feb 16	*	Mar 16	*	Apr 16	*	May 16	*	Jun 16	*
3	1	1811.5	0.0	1815.6	0.0	1824.2	0.3	1832.2	0.0	1840.1	0.5	*	*
33	31	1815.6	0.2	*	*	1832.2	0.0	*	*	*	*	*	*
34	*	*	*	*	*	*	*	*	*	*	*	*	*
35	MONTH TOTAL		4.1	*	8.3	*	8.3	*	7.4	*	0.5	*	0.0
36	*	*	*	*	*	*	*	*	*			*	*
37	*	*	*	*	*	Current Year Total:	28.6	*	Rolling 12-Mo Total:			*	*
Sheet 2

Excel Workbook
	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	*	Sheet 1	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
2	Day	Jan 15	*	Feb 15	*	Mar 15	*	Apr 15	*	May 15	*	Jun 15	*	Jul 15	*	Aug 15	*	Sep 15	*	Oct 15	*	Nov 15	*	Dec 15	*
3	1	1701.6	0.5	1707.6	0.0	1715.5	0.0	1724.3	0.4	1730.6	0.2	1739.2	0.2	1753.5	0.5	1768.1	0.1	1778.4	0.0	1783.5	0.3	1791.0	0.0	1801.1	0.2
33	31	1707.6	0.3	*	*	1723.9	*	*	*	1739.0	0.0	*	*	1768.0	0.8	1778.4	0.0	*	*	1791.0	0.4	*	*	1811.5	0.1
34	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
35	MONTH TOTAL		6.5	*	7.9	*	8.4	*	6.5	*	8.6	*	14.0	*	15.0	*	10.4	*	4.8	*	7.8	*	9.9	*	10.6
36	*	*	*	*	*	*	*	*	*			*	*	*	*	*	*	*	*	*	*	*	*	*	*
37	*	*	*	*	*	Current Year Total:	110.4	*	Rolling 12-Mo Total:			*	*	*	*	*	*	*	*	*	*	*	*	*	*
38	*	*	*	*	*	*	*	*	*			*	*	*	*	*	*	*	*	*	*	*	*	*	*
Sheet 1

The rolling 12 month sum should be the monthly sums from the last complete month before the current month back 12 months. Example: For this month (May), I would need to sum the month sums from May 2015 through April 2016.

The date headers Jan 2016 through Dec 2016 are at Sheet 2 and Sheet 1 (same but for 2015) cells C2, E2, G2 through Y2.
The monthly sums are at Sheet 1 and Sheet 2 cells D35, F35, H35 through Z35.


Excel 2010 and Windows 7

 

[konew1]

I put the current month into cell K37,
=SUMIFS(Sheet1!D35:Z35,Sheet1!C3:Y3,">"&DATE(YEAR(Sheet2!K37)-1,MONTH(Sheet2!K37),1))+SUMIFS(D35:Z35,C3:Y3,"<="&K37)

 

[dwgnome]

Thank you konew1 for providing the solution. I had to modify it a bit as the cell K37 already was occupied with the words “12-month rolling sum”. So I assigned K40 the current date (month). Also, since I did not want to include the current month in the 12-month rolling sum, I removed the equal sign after the less than sign. To get a full 12 months, I added an equal sign after the greater than sign. The formula with slight edits I ended up using in my actual workbook looks like:

=SUMIFS('AS0904-2015'!D35:Z35,'AS0904-2015'!C2:Y2,">="&DATE(YEAR(L42)-1,MONTH(L42),1))+SUMIFS(D35:Z35,C2:Y2,"<"&DATE(YEAR(L42),MONTH(L42),1))

I also created a second 12-month rolling sum that automatically uses the most current date so the user does not have to enter a date in K40:

=SUMIFS('AS0904-2015'!D35:Z35,'AS0904-2015'!C2:Y2,">="&DATE(YEAR(TODAY())-1,MONTH(TODAY()),1))+SUMIFS(D35:Z35,C2:Y2,"<"&DATE(YEAR(TODAY()),MONTH(TODAY()),1))

Probably not as efficient as having a reference cell with =TODAY() in it to reduce the number of date calls in the formula.

 

[konew1]

Thanks for feedback