Count to end of Range

[John]

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.

 

[Pete_UK]

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

 

[Spiky]

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.