Excel: How to sum rolling 12 months, variable data 1

[JRUKUSA]

Multiple years of Monthly columns, need to get rolling 12 months of data available. The Rows don't all have data to latest month. Therefore how do I sum the 12 months up to and including the latest month available. Can assume once latest month found add to it the prior 11 months even if they are zero/empty. Allowing for new months to be added in.

J F M A M J J A S O N D J r12mth
3 4 2 6 9 5 7 8 3 9 2 8 66

3 4 2 6 9 5 7 8 3 9 2 8 9 72

Any ideas?

Thanks in advance.

 

[SkipVought]

Hi,

Check out the OFFSET function which returns an array that you can SUM.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[JRUKUSA]

Thanks Skip,

Just started to play around with that function and it looks as though I have it apart from a blank month, if I set blank to zero it works.

Average(Offset(J2,0,Max(Count(J2:BM2)-12,0),1,12))

Appears to do what I need. J2 is first month record, BM2 is latest possible month. Max(count() gets me the last completed month.

Thanks again for the feed back.