Consider last 6 cells only

[Barney]

How can a formula consider and act on the last 6 items in a column where a
new item is added to the next blank cell each day. I want to keep all the
old records.

Using Win XP and Excel 2002.

Thanks in advance.

Barney

 

[Jim May]

In A1:A12 - Enter 1,2,3,4,5,6,7,8,9,10,11,12
In Cell B6 enter =SUM(OFFSET(A6,-5,0,6,1)) and copy down to B12
Sample using Offset()
HTH

 

[Bob Phillips]

Here is a formula that adds the last 6 items in a column A

=SUM(OFFSET(A1,COUNTA(A:A)-6,,6))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Barney]

Thanks Jim, that works, but my hope was that the result, 'SUM' in this case,
would always appear in the same cell.

 

[Bob Phillips]

See my post

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Barney]

Sorry but this isn't working for me. I always get 'zero'.

I have 1, 2, 3, 4, 5, 6, 7, and 8 in A1 thru A8. A20 should give me the sum
33 for A3:A8. When I add 9 in A9, the sum in A20 should be 39.

Thanks, Barney

 

[Tom Ogilvy]

=SUM(OFFSET(A1,COUNTA(A1:A19)-6,,6))

worked for me.

 

[Barney]

Thanks Tom and Jim. You were both right and very helpful. I figured out
what I was doing wrong.

Barney