Year to Date sum of Monthly data for multiple years

[bfoxworthy]

I feel like this should be pretty straightforward using a Sum and Offset function. I have monthly revenue data in a table as follows:

Excel 2010

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
Revenue
Year to Date Revenue
^YTD Sum starts over

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1/31/2012[/TD]
[TD="align: right"]2/29/2012[/TD]
[TD="align: right"]3/31/2012[/TD]
[TD="align: right"]4/30/2012[/TD]
[TD="align: right"]5/31/2012[/TD]
[TD="align: right"]6/30/2012[/TD]
[TD="align: right"]7/31/2012[/TD]
[TD="align: right"]8/31/2012[/TD]
[TD="align: right"]9/30/2012[/TD]
[TD="align: right"]10/31/2012[/TD]
[TD="align: right"]11/30/2012[/TD]
[TD="align: right"]12/31/2012[/TD]
[TD="align: right"]1/31/2013[/TD]
[TD="align: right"]2/28/2013[/TD]
[TD="align: right"]3/31/2013[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"] 173,995[/TD]
[TD="align: right"] 171,098[/TD]
[TD="align: right"] 180,910[/TD]
[TD="align: right"] 193,189[/TD]
[TD="align: right"] 282,645[/TD]
[TD="align: right"] 297,107[/TD]
[TD="align: right"] 345,414[/TD]
[TD="align: right"] 364,242[/TD]
[TD="align: right"] 372,352[/TD]
[TD="align: right"] 396,951[/TD]
[TD="align: right"] 379,082[/TD]
[TD="align: right"] 460,580[/TD]
[TD="align: right"] 470,766[/TD]
[TD="align: right"] 441,368[/TD]
[TD="align: right"] 506,636[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"] 173,995[/TD]
[TD="align: right"] 345,093[/TD]
[TD="align: right"] 526,003[/TD]
[TD="align: right"] 719,192[/TD]
[TD="align: right"] 1,001,837[/TD]
[TD="align: right"] 1,298,944[/TD]
[TD="align: right"] 1,644,358[/TD]
[TD="align: right"] 2,008,600[/TD]
[TD="align: right"] 2,380,953[/TD]
[TD="align: right"] 2,777,904[/TD]
[TD="align: right"] 3,156,986[/TD]
[TD="align: right"] 3,617,565[/TD]
[TD="align: right"] 470,766[/TD]
[TD="align: right"] 912,134[/TD]
[TD="align: right"] 1,418,770[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

I would like to be able to use a single formula to sum the year to date data based on the month. For example: February would include the sum of 2 months (January and February). My date inputs are dynamic from another sheet so I cannot simply use a Sum($C2:C2) style reference. When you get out to January 2013 the formula should only sum the single month.

I tried using a formula like this: =SUM(OFFSET(C2,0,0,,(MONTH(E$C1)))) but this doesn't work.

 

[Faseeh]

With your data in A1:Q4 try this:

=SUMPRODUCT((MONTH($C$1:$Q$1)<=MONTH(I6))*(YEAR(I6)=YEAR($C$1:$Q$1))*(C4:Q4))

where I6 contains the month want to sum up to.

Regards,

 

[MrVillareal]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			Sales Year To Date
2	Month	Feb-12	300
3		Jan-12	Feb-12	Mar-12	Apr-12	May-12	Jun-12	Jul-12	Aug-12	Sep-12	Oct-12	Nov-12	Dec-12
4	Sales	100	200	300	400	500	600	700	800	900	1000	1100	1200
Sheet1

Cell Formulas
Range	Formula
C2	=SUM(B4:INDEX(B4:M4,MATCH(B2,B3:M3,0)))

 

[bfoxworthy]

With your data in A1:Q4 try this:

=SUMPRODUCT((MONTH($C$1:$Q$1)<=MONTH(I6))*(YEAR(I6)=YEAR($C$1:$Q$1))*(C4:Q4))

where I6 contains the month want to sum up to.

Regards,

This seems to work, would there be any way to replace the sumproduct with an average? That way it is calculating a YTD average?

 

[bfoxworthy]

This seems to work, would there be any way to replace the sumproduct with an average? That way it is calculating a YTD average?

I figured out how to do the average: =(SUMPRODUCT((MONTH($C$1:$Q$1)<=MONTH(I6))*(YEAR(I6)=YEAR($C$1:$Q$1))*(C4:Q4)))/(MONTH(I6)

Thanks for all your help.