Year to date formula & Rolling 12 months

[audrey]

Hello,

I have a data set as following

Jan/11	Feb/11	Mar/11	Apr/11	May/11	Jun/11	Jul/11	Aug/11	Sep/11	Oct/11	Nov/11	Dec/11	Jan/12	Feb/12	Mar/12	Apr/12	May/12	Jun/12	Jul/12	Aug/12	Sep/12	Oct/12	Nov/12	Dec/12
14,360	17,584	18,839	15,593	15,766	21,620	27,760	17,016	19,940	24,472	22,312	23,360	19,963	22,308	21,132	25,586	25,628	21,440	0	0	0	0	0	0

<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

 

[Robert Mika]

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]

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]

So is that a Text format?

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

 

[audrey]

Hello again, yes it is a text format

 

[audrey]

I see only text

 

[Robert Mika]

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]

unfortunately it didnt work for me Robert, perhaps the formula should not be dependant on the month and years

 

[Robert Mika]

Are you getting any errors?
Are the dates are going to be in the same place.

 

[ab1275]

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()