Dynamic Range Sizes in Formulas

I had a question on variable length arrays within complicated array formulas, because the data size would change daily, but I couldn't use whole column references because it slowed everything down. While building my sample sheet for upload here, I had an idea and solved my own question. Thought I would share my solution here;

Scenario: I have values in Column K, starting at K2. I need to SUM them. The number of values is variable.

Example formula (correct output, slow calculation speed)
=SUM(K:K)


Solution:
=SUM(OFFSET(K2,,,COUNTA(K:K)))



Note: SUM formula is a placeholder. I know it is not a complicated array function, and I know that this specific example wouldn't be slow enough to need a solution like this. It is just to show how the range is replaced.

Note 2: This solution assumes no blank rows in Column K.

There is also a non volatile alternate, if you can reasonably estimate the maximum number of rows likely to be employed:

=SUM(K2:INDEX(K2:K10000,MATCH(9.99999999999E+307,K2:K10000,1)))

This will sum all entries between K2 and the last entry in column K (up to K10000), regardless of blanks.

N2=SUM(INDIRECT("K2"&":"&"K"&COUNTA($K$2:$K$50)+1))

See the attached file.

Originally Posted by sweep

There is also a non volatile alternate, if you can reasonably estimate the maximum number of rows likely to be employed:

=SUM(K2:INDEX(K2:K10000,MATCH(9.99999999999E+307,K2:K10000,1)))

This will sum all entries between K2 and the last entry in column K (up to K10000), regardless of blanks.

You just answered another thread for me. Thanks!