How can I refer to an entire column?

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

Guest

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!
 
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.
 
try this where 99999999 is larger than any possible number in col B
=SUM(b3:OFFSET(b3,MATCH(99999999,b:b),1))
 
Back
Top