Select End of list in formula?

R

robotman

Is there a way to have a formula dynamically select the end of a list.

For example I have a list in Col A that can range from 10 - 5000
entries. I want to dynamically sum this (without always just selecting
the max size because there is information below the list). I also want
to use a formula instead of a list or pivot table.

In visual basic the selection would be something like:
SUM (Range(Range("A1"),Range("A1").End(xldown)))

Any ideas?
 
B

Biff

Will there be any empty cells *within* the range of interest? Is there a
"delimiter" between the range of interest and the data below it (like an
empty cell)?

Here's one way assuming no empty cells *within* the range of interest and
there is at least one empty cell between data sets:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=SUM(A1:INDEX(A1:A10000,MATCH(TRUE,A1:A10000="",0)))

Biff
 
Top