How can I refer to an entire column?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Katyounge - 24 Sep 2007 06:34 GMT

This is probably a really simple thing to do, but I can't figure it out and
can't find it in the help anywhere.

I have a worksheet that has rows of data. I periodically enter more rows as
time goes on. I'd like to be able to write a formula that summarizes data
from a certain column. The formula is easy to figure out, but I can't figure
out how to refer to the entire column, and not just a range of cells in the
column.

For instance, I can say "=SUM(B3:B10)", but when I add data to row 11, my
formula will need to be updated. I could just pick a really high number (like
"B3:B1000"), but I may eventually get that many rows, and I don't want to
have to remember to check that my formula range is right. How can I say, "sum
all the data in all non-empty rows in column B underneath row 3?"

Thank you!

Reply to this Message

kassie - 24 Sep 2007 07:12 GMT

=SUM(B:B)

Signature

Hth

Kassie Kasselman
Change xxx to hotmail

> This is probably a really simple thing to do, but I can't figure it out and
> can't find it in the help anywhere.
[quoted text clipped - 12 lines]
>
> Thank you!

Reply to this Message

kassie - 24 Sep 2007 07:20 GMT

Ignore previous post!

Did not see the bit about starting in row 4.

It really depends what you have in B1:B3. If those cells contain labels,
then
=SUM(B:B) will work.

If you have quantities/amounts in these cells, then something like
=SUMB:B)-SUM(B1:B3) would work, or alternatively,
=SUM(B3:B65536), inless you use Excel 2007.

Signature

Hth

Kassie Kasselman
Change xxx to hotmail

> This is probably a really simple thing to do, but I can't figure it out and
> can't find it in the help anywhere.
[quoted text clipped - 12 lines]
>
> Thank you!

Reply to this Message

Rich - 24 Sep 2007 12:34 GMT

=SUM(B3:B65536)

> This is probably a really simple thing to do, but I can't figure it out and
> can't find it in the help anywhere.
[quoted text clipped - 12 lines]
>
> Thank you!

Reply to this Message

Don Guillett - 24 Sep 2007 14:10 GMT

try this where 99999999 is larger than any possible number in col B
=SUM(b3:OFFSET(b3,MATCH(99999999,b:b),1))

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> This is probably a really simple thing to do, but I can't figure it out
> and
[quoted text clipped - 18 lines]
>
> Thank you!

Reply to this Message