How can I refer to an entire column?
 

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!

How can I refer to an entire column?
 

=SUM(B:B)
--
Hth

Kassie Kasselman
Change xxx to hotmail


"Katyounge" wrote:

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!

How can I refer to an entire column?
 

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.

--
Hth

Kassie Kasselman
Change xxx to hotmail


"Katyounge" wrote:

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!

How can I refer to an entire column?
 

=SUM(B3:B65536)




"Katyounge" wrote:

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!

How can I refer to an entire column?
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Katyounge" wrote in message
...
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!