In Excel: select the last 20 rows of data in a column

Thread view:

Thread rating:

Tad Blanchard - 23 Nov 2005 21:22 GMT

How do I create a formula that will select the last 20 entries in a column of
a database?

Bob Phillips - 23 Nov 2005 21:46 GMT

This is the sort of thing

OFFSET($A$1,COUNTA(A:A)-20,0,20,1)

which can be used like so

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

Signature

HTH

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

> How do I create a formula that will select the last 20 entries in a column of
> a database?

Krishnakumar - 25 Nov 2005 05:47 GMT

You can avoid the Volatile OFFSET function.

Try,

=SUM(INDEX(A:A,COUNTA(A:A)-19):INDEX(A:A,COUNTA(A:A)))

HTH

Signature

Krishnakumar

Bob Phillips - 25 Nov 2005 09:35 GMT

Yeah, 5 functions for 3. Can't see much rationale in that.

Signature

HTH

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

> You can avoid the Volatile OFFSET function.
>
[quoted text clipped - 3 lines]
>
> HTH