Year to date formula & Rolling 12 months

audrey

Active Member
Joined
Jul 30, 2008
Messages
491
Hello,

I have a data set as following

Jan/11
Feb/11Mar/11Apr/11May/11Jun/11Jul/11Aug/11Sep/11Oct/11Nov/11Dec/11Jan/12Feb/12Mar/12Apr/12May/12Jun/12Jul/12Aug/12Sep/12Oct/12Nov/12Dec/12
14,36017,58418,83915,59315,76621,62027,76017,01619,94024,47222,31223,36019,96322,30821,13225,58625,62821,440000000

<colgroup><col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706; width:56pt" span="12" width="74"> </colgroup><tbody>
</tbody>


Automatic YTD formula ???

Lets assume in A1 cell I have a dropdown of numbers from 1-12, if 7 is selected, the formula will take the the sum of only from Jan/12 to Jul/12 or if 4 is selected formula will only take the sum of Jan/12 to Apr/12 and so on...

Rolling 12 months ???

Same principle, if 7 is selected in cell A1, the formula will take the sum Jul/12 back to Jun/11, in other words sum of previous 12 rows starting from the Jul/12 and so on....

I thought offset could work, but apparaently I make something wrong in the formula!

=SUM(OFFSET(B1,COUNTIF(B1:B12,A1)-12,0,12,1))


I will be very happy if someone could help me out!

thank you

Aude
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
Assuming your data are in Excel format dd/mm/yy
YTD
=SUMPRODUCT(--(YEAR(A2:X2)=YEAR(TODAY())),--(MONTH(A2:X2)>=1),--(MONTH(A2:X2)<=A1)*A3:X3)
Rolling

If you want Jul12 back to Jun 11 that would be 14 months I assume you want Jul back to AUg -12 months
=SUM(OFFSET(A2,1,MATCH(DATE(YEAR(TODAY()),A1,1),$A$2:$X$2,0)-12,1,12))
Otherwise play with 12.
 

audrey

Active Member
Joined
Jul 30, 2008
Messages
491
Hello robert

Thanks for your reply, however my data is not in dd/mm/yy format and unfortunately I am not allowed to change it as it comes from the system
 

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
So is that a Text format?

What do you see in formula bar?
A date(01-07-12) or JUL/12
 

audrey

Active Member
Joined
Jul 30, 2008
Messages
491

ADVERTISEMENT

Hello again, yes it is a text format
 

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256

ADVERTISEMENT

Try:
=SUMPRODUCT(--(YEAR(DATEVALUE(A2:X2))=YEAR(TODAY())),--(MONTH(DATEVALUE(A2:X2))>=1),--(MONTH(DATEVALUE(A2:X2))<=A1)*A3:X3)

and
=SUM(OFFSET(A2,1,MATCH(DATE(YEAR(TODAY()),A1,1),DATEVALUE($A$2:$X$2),0)-12,1,12))
(confirm this Formula with Control+Shift+Enter)
 

audrey

Active Member
Joined
Jul 30, 2008
Messages
491
unfortunately it didnt work for me Robert, perhaps the formula should not be dependant on the month and years
 

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
Are you getting any errors?
Are the dates are going to be in the same place.
 

ab1275

New Member
Joined
Jul 19, 2016
Messages
29
Office Version
  1. 365
Platform
  1. Windows
Hi Robert,

I am in a similar situation but my data is for over 2 year period. Will the formula still work? I can use mm/dd/yyyy or text of MMM YY.

I would like to select the last month of the 12 month period instead of using today()
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

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
Top