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?

=SUM(A10:INDIRECT("A"&MAX(IF(A10:A5000<"",ROW(A10 :A5000)))))
paste then hit ctrl+shift+enter

" skrev:

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?

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

wrote in message
oups.com...
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?