Year-to-Date (YTD) Formula Help

[wakefield101]

Can anyone please help me figure out a formula to calculate YTD??

What I am trying to achieve:

Sum all months <= current month, ONLY IF months fall within the same year CY (Calendar Year) as the current month

As you can see on the screenshot below, Cell B5 indicates the current month. The timeline below (row 16) only shows a portion of what the actual timeline is. This project goes out to 2019.

Does anyone have any thoughts??

Excel Workbook
	A	B	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3	Study Start Date	May-15	*	*	*	*	*	*	*	*	*	*	*	*	*	*
4	Study End Date	Feb-19	*	*	*	*	*	*	*	*	*	*	*	*	*	*
5	Current Month	Oct-15	*	*	*	*	*	*	*	*	*	*	*	*	*	*
6	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
14	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
15	*	*	% Complete YTD	*	*	*	*	*	*	*	*	*	*	*	*	*
16	Service Fee	*	*	Apr-15	May-15	Jun-15	Jul-15	Aug-15	Sep-15	Oct-15	Nov-15	Dec-15	Jan-16	Feb-16	Mar-16	Apr-16
18	b. Latest Forecast / Accrual	*	*	*	* * * *378,027	* * * * * * * * - *	* * * *144,010	* * * * * * * * - *	* * * *324,023	* * * *180,013	* * * *270,020	* * * *270,020	* * * *198,014	* * * *126,009	* * * *324,023	* * * * * * * * - *
23	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
Summary

 

[sheetspread]

See if this helps:

http://www.mrexcel.com/forum/excel-questions/901660-sum-mtd-ytd-formulas.html

 

[wakefield101]

Hi Sheetspread,

thanks for the help, that link did help me find a short term solution:

=IFERROR(SUMPRODUCT(--(YEAR($D$19:$FE$19)=YEAR($B$5)),--($D$19:$FE$19<=DATE(YEAR($B$5),MONTH($B$5),DAY((EOMONTH($B$5,0))))),D21:FE21)/SUMPRODUCT(--(YEAR($D$19:$FE$19)=YEAR($B$5)),--($D$19:$FE$19<=DATE(YEAR($B$5),MONTH($B$5),DAY((EOMONTH($B$5,0))))>=DATE(YEAR($B$5),MONTH($B$5),DAY((EOMONTH($B$5,0))))),D21:FE21),"-")

HOWEVER, I noticed that when I changed the end date (see my initial post: Cell B4), my equation all of a sudden did not work... I receive the #VALUE! error...

Would you happen to know how to fix this issue?

 

[sheetspread]

I'm not sure your formula needs to be so complicated. If we could go back to:

Sum all months <= current month, ONLY IF months fall within the same year CY (Calendar Year) as the current month

How does this look?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	Study Start Date	15-May
4	Study End Date	19-Feb
5	Current Month	15-Oct		1,026,073
6
7
8			% Complete YTD
9	Service Fee			15-Apr	15-May	15-Jun	15-Jul	15-Aug	15-Sep	15-Oct	15-Nov	15-Dec	16-Jan	16-Feb	16-Mar	16-Apr
10	b. Latest Forecast / Accrual				378,027	-	144,010	0	324,023	180,013	270,020	270,020	198,014	126,009	324,023
Sheet1

Cell Formulas
Range	Formula
D5	=SUMIFS($D$10:$P$10,$D$9:$P$9,"<="&B5,$D$9:$P$9,">="&DATE(YEAR(B5),1,1))

 

[wakefield101]

I had to modify it a tad bit but that equation worked!!

Thank you for helping me simplify!!