Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2
Default 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!
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 515
Default 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!

  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 515
Default 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!

  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 47
Default 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!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Refer to column width in conditional formatting sammy Excel Discussion (Misc queries) 4 September 7th 07 02:43 AM
In VBA, How to Refer to Cell In Specific Column But Selected Rows? PBJ Excel Discussion (Misc queries) 7 August 3rd 07 05:56 PM
Countif forumulas that refer to more than one column Karl Excel Worksheet Functions 4 March 6th 07 01:11 AM
How to refer to current column in a formula? jmg092548 Excel Discussion (Misc queries) 4 August 10th 05 08:00 PM
Trying to get a column of numbers to refer back Overbaked Excel Worksheet Functions 1 December 30th 04 08:36 PM


All times are GMT +1. The time now is 10:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"