Summing a column

[Guest]

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.

 

[Don Guillett]

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

 

[Guest]

Set logic aside.

=SUM(A$5:A65536)

will never fail you. (unless you are using Excel 2007)

 

[Dave Peterson]

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.

 

[Guest]

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

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

 

[Don Guillett]

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
(e-mail address removed)

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