rolling 12 months

[Guest]

I would like to set up my spreadsheet to give me a running 12 month total, by
dropping the oldest data and using my current data. i.e. january thru dec
totals then in next jan the previous year would drop off when I input current
jan totals.

Thanks again for your help

D

 

[Max]

One way ..

Assume the set-up below, months in col A, from row2 down,
the values in col B, & the rolling 12 month total to be in B1

Mth-Yr <R12m-Total>
Aug-04 50
Sep-04 80
Oct-04 70
Nov-04 10
Dec-04 50
Jan-05 90
Feb-05 20
Mar-05 90
Apr-05 40
May-05 40
Jun-05 70
Jul-05 40
....

Put in B1:

=SUM(OFFSET($A$1,MATCH(OFFSET($A$1,COUNTA(A:A)-1,0),A:A,0)-1,1,-12))

 

[Arvi Laanemets]

Hi

This assumes there is a single row for every moth, and always a row for a
month, does it? More general solution (dates in column A, values in column
B, headers in row 1, sum is placed outside of data range, p.e. in cell C2)
=SUMIF(A:A,">"&DATE(YEAR(MAX(A:A)),MONTH(MAX(A:A))-11,0),B:B)

 

[Max]

This assumes there is a single row for every moth,
and always a row for a month, does it?

More general solution (dates in column A, values in column
B, headers in row 1, sum is placed outside of data range, p.e. in cell C2)
=SUMIF(A:A,">"&DATE(YEAR(MAX(A:A)),MONTH(MAX(A:A))-11,0),B:B)

Just a thought. The above would return zero if the dates were not "real"
dates.
(A possibility that I had guessed might be the case, in the earlier
response. It wasn't clear from the orig. post.)

 

[Arvi Laanemets]

Hi

Just a thought. The above would return zero if the dates were not "real"
dates.
(A possibility that I had guessed might be the case, in the earlier
response. It wasn't clear from the orig. post.)

P.e. month names as text?
It's simply - then OP will be in trouble

(Of-course even then it's possible, but the formula will be huge. It's main
reason I always advice to use real dates - when there is a need, then you
always can format them to be displayed as month names. But my 1st preference
will be the format "yyyy.mm", which allows to sort data properly.)