Summing a column

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
One way where 9999 is any number larger than possible in your column
=SUM(A5:OFFSET(A5,MATCH(9999,A:A),0))
 
Set logic aside.

=SUM(A$5:A65536)

will never fail you. (unless you are using Excel 2007)
 
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.
 
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 said:
One way where 9999 is any number larger than possible in your column
=SUM(A5:OFFSET(A5,MATCH(9999,A:A),0))
Click to expand...
 
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)
Tevuna said:
Your formula doesn't seem to work. If the number is larger than possible
n/a
is returned. Could you further elaborate? Please.
Click to expand...
 
Back
Top