| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search end of data in column
I usally import data that varies in rows, sometimes it generates 1000
rows sometimes 2000 rows, again it varies. I have a forumula that searches a range, for example, one forumula sums all the values in B2:B10000. I set my range to 10000 because I never know how many rows I am going to import. Is there a formula that I can use that allows me to find the end of the data in a column? James |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search end of data in column
Try this:
=SUM(B2:INDEX(B2:B65536,COUNT(B:B))) Assumes no empty cells *within* the range. Biff wrote in message ups.com... I usally import data that varies in rows, sometimes it generates 1000 rows sometimes 2000 rows, again it varies. I have a forumula that searches a range, for example, one forumula sums all the values in B2:B10000. I set my range to 10000 because I never know how many rows I am going to import. Is there a formula that I can use that allows me to find the end of the data in a column? James |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search end of data in column
You could try something like
=sum(offset(B2,0,0,counta(B:B),1) If you have a header in B1, it would be =sum(offset(B2,0,0,counta(B:B)-1,1) " wrote: I usally import data that varies in rows, sometimes it generates 1000 rows sometimes 2000 rows, again it varies. I have a forumula that searches a range, for example, one forumula sums all the values in B2:B10000. I set my range to 10000 because I never know how many rows I am going to import. Is there a formula that I can use that allows me to find the end of the data in a column? James |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search end of data in column
Fri, 11 May 2007 12:01:00 -0700 from Barb Reinhardt
: " wrote: I set my range to 10000 because I never know how many rows I am going to import. Is there a formula that I can use that allows me to find the end of the data in a column? You could try something like =sum(offset(B2,0,0,counta(B:B),1) If you have a header in B1, it would be =sum(offset(B2,0,0,counta(B:B)-1,1) Barb, Is there a variant that works when there are some empty cells within the range? -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
| Search Column Data and Return Multiple Values across Row | Excel Worksheet Functions | |||
| Search for data in a column bring all related items in other colum | New Users to Excel | |||
| two column search | Excel Discussion (Misc queries) | |||
| Search one column and return value from next column | Excel Discussion (Misc queries) | |||
Hybrid Mode
