Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count to end of Range
Is there a way to count to the end of a column when you are not sure how long
the range is? I have functions that use the SUMPRODUCT to count various series of data in other worksheets, but each month new data is added to those worksheets and I have to keep editing the functions. Otherwise I have to use set the functions to count out well beyond the data which is slowing down Excel. Any help would be apprechiated. Using Excel 2003. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count to end of Range
You could use dynamic named ranges, as described here on Chip
Pearson's site: http://www.cpearson.com/excel/named.htm#Dynamic The ranges would automatically adjust themselves to suit the amount of data that you have. You can use the names in your formula instead of actual cell references. Hope this helps. Pete On Apr 24, 3:48*pm, John wrote: Is there a way to count to the end of a column when you are not sure how long the range is? I have functions that use the SUMPRODUCT to count various series of data in other worksheets, but each month new data is added to those worksheets and I have to keep editing the functions. Otherwise I have to use set the functions to count out well beyond the data which is slowing down Excel. Any help would be apprechiated. Using Excel 2003. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count to end of Range
On Apr 24, 9:48 am, John wrote:
Is there a way to count to the end of a column when you are not sure how long the range is? I have functions that use the SUMPRODUCT to count various series of data in other worksheets, but each month new data is added to those worksheets and I have to keep editing the functions. Otherwise I have to use set the functions to count out well beyond the data which is slowing down Excel. Any help would be apprechiated. Using Excel 2003. Here's a simple sum to add up a dynamic range. You can probably modify this to your purpose if you can write a SUMPRODUCT. ;-) =SUM(C2:INDEX(C:C,COUNTA(C:C)-1)) This assumes a column title in C1 and contiguous data, and counts the number of cells with data and creates a range down to the last cell. Now, you are probably starting from a row other than 1 and you probably have titles or blanks up at the top. So you need to subtract (for titles) or add (for the blanks) to the count to get the proper number of cells from C1 down to the bottom of the list. If it isn't nice, contiguous data, something else would probably be better. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Criteria, Count If, Sum Product to get count across range | Excel Worksheet Functions | |||
Using a count in a range name | Excel Discussion (Misc queries) | |||
Sum or Count using set range | Excel Worksheet Functions | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
Count cells in one range based on parameters in another range | Excel Worksheet Functions |