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
1		1/31/2012	2/29/2012	3/31/2012	4/30/2012	5/31/2012	6/30/2012	7/31/2012	8/31/2012	9/30/2012	10/31/2012	11/30/2012	12/31/2012	1/31/2013	2/28/2013	3/31/2013
2	Revenue	173,995	171,098	180,910	193,189	282,645	297,107	345,414	364,242	372,352	396,951	379,082	460,580	470,766	441,368	506,636
3
4	Year to Date Revenue	173,995	345,093	526,003	719,192	1,001,837	1,298,944	1,644,358	2,008,600	2,380,953	2,777,904	3,156,986	3,617,565	470,766	912,134	1,418,770
5														^YTD Sum starts over

<tbody>
</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.