YTD Plan (budget amount) to current month

Terrifro

New Member
Joined
Jul 18, 2018
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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
 
Upvote 0
Something like:


Excel 2010
HIJKLMNOPQRST
4JulyAugustSeptemberOctoberNovemberDecemberJanuaryFebruaryMarchAprilMayJune
5
6
7
8
9
10
11YTD
1275408275563840682078882475
Sheet5
Cell Formulas
RangeFormula
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
 
Upvote 0
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 :)
 
Upvote 0
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.
 
Upvote 0
Do you mean monthly percent of YTD like this:


Excel 2010
HIJKLMNOPQRST
4JulyAugustSeptemberOctoberNovemberDecemberJanuaryFebruaryMarchAprilMayJune
5
6
7
8
9
10
11YTD
1275408275563840682078882475
13
14
15
16
17
18
19
20
21100.00%
Sheet2
Cell Formulas
RangeFormula
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
This?:


Excel 2010
HIJKLMNOPQRST
4JulyAugustSeptemberOctoberNovemberDecemberJanuaryFebruaryMarchAprilMayJune
5
6
7
8
9
10
11YTD
1275408275563840682078882475
13
14
15
16
17
18
19
20
2110.00%7.10%9.00%10.00%
Sheet2
Cell Formulas
RangeFormula
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)
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top