Is it possible to do a rolling sum of 12 months that changes with the 1st of a new month?

[JWPratt8]

Hi All,

i'm trying to get a set of figures to sum over the last 12 months automatically. Therefore i want to be able to sum up the last 12 months figures and when the 1st of a new month commences i want the formula to essentially move along so that the last 12 months are up to date. For example:

If this month is Jun-13, i want to total Jul-12:Jun-13 then when the date is July 1st i want the total to be Aug-12:Jul-13.

I tried using a Sum, Offset & Count formula: [=SUM(OFFSET(L4,0,COUNT(L4:BB4)-12,1,12))] which would have worked if there weren't already figures in the up and coming months (cannot change this as the cells contain formula's that calculate future figures, which is essentially calculated as 0 due the fact that the future motnhs havent yet occured, but this formula reads this as a current figure and reads the incorrect last 12 months).

My months are on row 2 and span from F:BB, my figures are on row 4 and span from F:BB.

Can anyone help me with this?


Many Thanks,

James.

 

[jai9]

Hi,

Try this.

=SUM(R4,OFFSET(R4,0,-12))

Jai

 

[JWPratt8]

Something went wrong there, it came back as "False". I'm just looking for the sum of the latest 12 months on a rolling monthly basis.


James.