Calculate a growing list of data

I'm going to repost this, hoping desperately that someone can help.

I have a worksheet that has 2 columns of data that are going to grow everyday. The data in the columns is used to populate another worksheet in the same workbook. Currently I calculate the second worksheet using this function:
{=SUM(('Raw Hands'!A1:A64000<>"")*(LEFT('Raw Hands'!B1:B64000,1)=LEFT('Raw Hands'!A1:A64000,1)))} (basically it counts the number of instances where the first character in column A is the same as the first character in column B in the same row)
and it takes about 15 seconds after each entry into the Raw Hands worksheet. Right now the 2 columns in Raw Hands only have about 1000 entries and I'm adding about 200 a day, but as you can see, the array function calculates down to row 64000. Is there a way to calculate only the cells that contain data in Raw Hands (and automatically capture new data as I add it)?

Thanks.

Assuming that your data is made up of text values, try the following...

First, define your dynamic range...

Insert > Name > Define

Name: DynRange
Refers to:
='Raw Hands'!$A$1:INDEX('Raw Hands'!$A:$A,MATCH("*",'Raw Hands'!$A:$A,-1))

Then, try the following formula...

=SUM((DynRange<>"")*(LEFT(DynRange)=LEFT(OFFSET(DynRange,0,1))))

...confirmed with CONTROL+SHIFT+ENTER.

If your data is made up of numerical values...

Name: DynRange
Refers to:
='Raw Hands'!$A$1:INDEX('Raw Hands'!$A:$A,MATCH(9.99999999999999E+307,'Raw Hands'!$A:$A))

Hope this helps!

Originally Posted by jhalverson

I'm going to repost this, hoping desperately that someone can help.

I have a worksheet that has 2 columns of data that are going to grow everyday. The data in the columns is used to populate another worksheet in the same workbook. Currently I calculate the second worksheet using this function:
{=SUM(('Raw Hands'!A1:A64000<>"")*(LEFT('Raw Hands'!B1:B64000,1)=LEFT('Raw Hands'!A1:A64000,1)))} (basically it counts the number of instances where the first character in column A is the same as the first character in column B in the same row)
and it takes about 15 seconds after each entry into the Raw Hands worksheet. Right now the 2 columns in Raw Hands only have about 1000 entries and I'm adding about 200 a day, but as you can see, the array function calculates down to row 64000. Is there a way to calculate only the cells that contain data in Raw Hands (and automatically capture new data as I add it)?

Thanks.