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. |
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. |
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. |
All times are GMT +1. The time now is 03:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com