In summing a column range...say =sum(a$5:a175). If the column is
ever-growing, continually adding to the range, how can I alter the formula to
include the new items beyond a175? It doesn't seem logical that I should put
in some ridiculously large number which would include a bunch of blank cells.
--
Rich D
Armstrong Custom Homes
Redmond

One way where 9999 is any number larger than possible in your column
=SUM(A5:OFFSET(A5,MATCH(9999,A:A),0))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rich D" wrote in message
...
In summing a column range...say =sum(a$5:a175). If the column is
ever-growing, continually adding to the range, how can I alter the formula
to
include the new items beyond a175? It doesn't seem logical that I should
put
in some ridiculously large number which would include a bunch of blank
cells.
--
Rich D
Armstrong Custom Homes
Redmond

Set logic aside.

=SUM(A$5:A65536)

will never fail you. (unless you are using Excel 2007)
--
Gary''s Student - gsnu200742


"Rich D" wrote:

In summing a column range...say =sum(a$5:a175). If the column is
ever-growing, continually adding to the range, how can I alter the formula to
include the new items beyond a175? It doesn't seem logical that I should put
in some ridiculously large number which would include a bunch of blank cells.
--
Rich D
Armstrong Custom Homes
Redmond

If A1:A4 contains text (no real numbers!), you could use:
=sum(a:a)

if it contains numbers that you don't want included (even dates!):
=sum(a:a)-sum(a1:a4)


Lots of worksheet functions will limit themselves to the used range--no matter
how big you make the range in the formula.


Rich D wrote:

In summing a column range...say =sum(a$5:a175). If the column is
ever-growing, continually adding to the range, how can I alter the formula to
include the new items beyond a175? It doesn't seem logical that I should put
in some ridiculously large number which would include a bunch of blank cells.
--
Rich D
Armstrong Custom Homes
Redmond

--

Dave Peterson

Your formula doesn't seem to work. If the number is larger than possible n/a
is returned. Could you further elaborate? Please.

"Don Guillett" wrote:

One way where 9999 is any number larger than possible in your column
=SUM(A5:OFFSET(A5,MATCH(9999,A:A),0))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rich D" wrote in message
...
In summing a column range...say =sum(a$5:a175). If the column is
ever-growing, continually adding to the range, how can I alter the formula
to
include the new items beyond a175? It doesn't seem logical that I should
put
in some ridiculously large number which would include a bunch of blank
cells.
--
Rich D
Armstrong Custom Homes
Redmond

If the largest possible number in your range is 9998, use 9999 or
999999999999

=SUM(A5:OFFSET(A5,MATCH(9999,A:A),0))


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tevuna" wrote in message
...
Your formula doesn't seem to work. If the number is larger than possible
n/a
is returned. Could you further elaborate? Please.

"Don Guillett" wrote:

One way where 9999 is any number larger than possible in your column
=SUM(A5:OFFSET(A5,MATCH(9999,A:A),0))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rich D" wrote in message
...
In summing a column range...say =sum(a$5:a175). If the column is
ever-growing, continually adding to the range, how can I alter the
formula
to
include the new items beyond a175? It doesn't seem logical that I
should
put
in some ridiculously large number which would include a bunch of blank
cells.
--
Rich D
Armstrong Custom Homes
Redmond