Last 12 entries in a range

J

Jumbo Jimbo

Hi
This must be easy! Can anyone gimme a formula that only
counts the last 12 entries in a range? I'm constructing a
table at work - I only need the previous 12 months data
totalled (it's a rolling 12 month total I need). Each
month, I'll add another column for that months figure - I
need the formula to automatically include the new column
and the previous 11.
A formula is great but if anyone can advise maybe a better
way I'll happily take it on board.

Cheers

JJ
 
F

Frank Kabel

Hi
so if you want for example sum the last 12 entries try:
=SUM(OFFSET($A$1,COUNTA(A:A)-1,0,-12))

This requires at least 12 entires and does not allow blank
lines in between
 
I

icestationzbra

frank,

i was about to post this formulae for similar effect:

=SUM(A:A)-SUM(A1:(INDIRECT("a" & (COUNT(A:A)-12))))

and then i realised that the OP wanted sum across several columns (if
understand the question right).

in which case, do you think either of our formulae would work?

thanks,

mac
 
J

Jumbo Jimbo

Hi
Yes, my data will be in row 2. A1 - L1 will be Jan -
December, A2 - L2 will be the monthly figure. I'd like to
place a formula in M2 to sum the previous 12 figures (easy
I know) BUT when I insert a new column before M for Jan, I
want the formula (which will now be in cell N2) to sum
only the previous 12 cells. Same again for Feb etc etc.
Hope this is clear enough for you guys, and thanks for
your help.
JJ
 
Top