Adding last three entries

[Biff]

Hi Mike,

Here's one way - adjust ranges as needed:

=SUM(OFFSET(A1,COUNTA(A:A),0,-4))

Biff

 

[Myrna Larson]

Just wanted to point out that the formula requires that there be no embedded blank cells in
column A.

 

[JMay]

Why "- 4"?
TIA,

Hi Mike,

Here's one way - adjust ranges as needed:

=SUM(OFFSET(A1,COUNTA(A:A),0,-4))

Biff

 

[Anon]

Assume, for example, that there are 5 entries in column A (i.e. A1:A5). The
start of the sum range is offset from A1 by 5 rows; that is, it is A6. So
the height needs to be -4 (i.e. sum range A6:A3) in order to add the last
three entries (A3:A5).

 

[JMay]

Thanks Anon for the explanation.
I better understand the Offset() function now.
I suppose as an alternative I would now enter the same formula as:

=SUM(OFFSET(A1,COUNTA(A:A)-1,0,-3)) << the set my orig row count
(movement down) to row 4 (from position A1), then bring back the 3 cell
range (to relate to the 3 rows I want) area in my Sum().. Right?

I wouldn't have understood this without your assistance, thanks again.

 

[Anon]

This is correct. Personally, I would write the formula as
=SUM(OFFSET(A1,COUNTA(A:A)-3,0,3))
as I find it easier to think of ranges in a 'forwards' direction (such as
A3:A5, rather than A5:A3).
However, all three versions give the same result, so it's just a matter of
personal preference.