YTD Plan (budget amount) to current month

[Terrifro]

I currently have a scorecard and it has actual(to be entered per month) and plan(budgeted for the each month) for year. I am wanting to calculate a YTD sum for up to the current month only on the plan(budget) amounts.

H4:S4 are the months (July to June) - I have hidden a column H3:S3 numbering the months (1 to 12)

My Plan(budget) figures are H12:S12 and YTD is T12

Even though I have a figure in each month for Plan(budget) I only want the YTD to calculate to current month.

Eg: YTD Plan figure for the the month of August should only equal July and August figure

I currently have this formula however I want it to remove the months we aren't in yet. It is currently totaling the YTD (July to June)

=SUMIF(H3:S3,"<="&TODAY(),H12:S12)

Please help

 

[Terrifro]

I also currently have a drop down cell which I would like to use as showing the current months YTD amount.
2:G (this currently has JULY to JUNE as a selection)

Can this be incorporated into the formula so if I choose the month of August in the drop down (the month of reporting) it will only show the YTD up to AUGUST

 

[sheetspread]

Something like:

Excel 2010
	H	I	J	K	L	M	N	O	P	Q	R	S	T
4	July	August	September	October	November	December	January	February	March	April	May	June
5
6
7
8
9
10
11													YTD
12	75	40	82	75	56	38	40	68	20	78	88	24	75
Sheet5

Cell Formulas
Range	Formula
T12	=SUM(OFFSET($H$12:$S$12,,,,MATCH(TEXT(TODAY(),"MMMM"),$H$4:$S$4,0)))

where today() can instead be a cell reference/dropdown

 

[Terrifro]

Thank you so much it works great, I ever changed Today() to the drop down cell and it is working beautifully. Thanks again for your help

 

[Terrifro]

Hi sheetspread, I am wondering if you can help me again. Your above formula for the YTD $ figure per month works great, I want to use the same formula (for example row H21 for percentages per month.
Can you assist please? Thank you in advance.

 

[sheetspread]

Do you mean monthly percent of YTD like this:

Excel 2010
	H	I	J	K	L	M	N	O	P	Q	R	S	T
4	July	August	September	October	November	December	January	February	March	April	May	June
5
6
7
8
9
10
11													YTD
12	75	40	82	75	56	38	40	68	20	78	88	24	75
13
14
15
16
17
18
19
20
21	100.00%
Sheet2

Cell Formulas
Range	Formula
T12	=SUM(OFFSET($H$12:$S$12,,,,MATCH(TEXT(TODAY(),"MMMM"),$H$4:$S$4,0)))
H21	=IF(MONTH(TODAY())>=COLUMN(G1),H12/$T$12,"")

or something else?

 

[Terrifro]

I have percentages listed for each month - eg H21 (10%), H=I21 (7.1%), J21 (9%) etc.
So again I want the same formula as the $ for each month =SUM(OFFSET($H$12:$S$12,,,,MATCH(TEXT(TODAY(),"MMMM"),$H$4:$S$4,0))) but now I want it to show the percentage for the current month in the YTD column once we choose the drop down box for the month.

I hope this makes sense.

 

[Terrifro]

SO I don't need to find the percentage of a figure or the total percentage. I just want the YTD to show the current months percentage once the drop down month has been selected.

 

[sheetspread]

This?:

Excel 2010
	H	I	J	K	L	M	N	O	P	Q	R	S	T
4	July	August	September	October	November	December	January	February	March	April	May	June
5
6
7
8
9
10
11													YTD
12	75	40	82	75	56	38	40	68	20	78	88	24	75
13
14
15
16
17
18
19
20
21	10.00%	7.10%	9.00%										10.00%
Sheet2

Cell Formulas
Range	Formula
T12	=SUM(OFFSET($H$12:$S$12,,,,MATCH(TEXT(TODAY(),"MMMM"),$H$4:$S$4,0)))
T21	=HLOOKUP(TEXT(TODAY(),"MMMM"),$H$4:$S$21,18,0)

 

[Terrifro]

Thank you it works great