How to calculate YTD Actuals and ignore future month's forecast

[jekl1000]

Hi all,

I maintain a spreadsheet that tracks monthly actuals and forecasts for future months. In the "YTD Actuals" and "Remaining Forecast" fields I have to change the formula each month to accommodate there being one more month of Actuals and one less month of Remaining data.

I have seen suggestions to use SUMIF or OFFSET but not sure those work for how I have the data laid out in my spreadsheet. Does anyone have a dynamic formula (for M2 in the image below) that would only sum the months up to the current month? It would also be great to have another formula for N3 that only sums remaining month's data.

Any help would be greatly appreciated

 

[awoohaw]

try this:
It requires your month headers to be date values. Also, this has the TODAY() function embeded, it will change everymonth whether you like it or not.
You may have to enter it with the CNTL-SHFT-ENTR keystroke combination, depending on your version (you should update that in your profile).

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	YTD Actuals	Remaining Forecast	EOY Estimate
2	1	1	1	1	1	1	1	1	1	2	2	2	9	6	15
Sheet1

Cell Formulas
Range	Formula
B1:L1	B1	=EDATE(A1,1)
M2	M2	=SUM((MONTH(TODAY())>MONTH($A$1:$L$1))*($A$2:$L$2))
N2	N2	=SUM((MONTH(TODAY())<=MONTH($A$1:$L$1))*($A$2:$L$2))
O2	O2	=SUM(M2:N2)

 

[jekl1000]

try this:
It requires your month headers to be date values. Also, this has the TODAY() function embeded, it will change everymonth whether you like it or not.
You may have to enter it with the CNTL-SHFT-ENTR keystroke combination, depending on your version (you should update that in your profile).

This worked perfect; thank you so much! I had the headers as a date but they are formatted to show MMM. I wish I had done this a long time ago because I actually have 20+ line items and the manual updates each month in these columns get missed sometimes. Thank you again!

 

[awoohaw]

You're welcome. Thanks for the the feedback. If your timeline has multiple years then you'll need to filter for the year as well. In your example it does not seem that way so it I don't think it is needed.

Best Wishes!