Excel Formula: Dynamic COUNTA

[2Took]

Is there a way to replace the "A5000" in below formula such that formula automatically adjusts to however many rows with data, instead of limiting to 5000, but without just putting a million rows?

=COUNTA(A11:A5000)

 

[jasonb75]

Are there likely to be empty rows in the middle of the range? If not then you could use

=MATCH("zzz",A:A)-10

assuming text data rather than numeric.

 

[2Took]

Are there likely to be empty rows in the middle of the range? If not then you could use

=MATCH("zzz",A:A)-10

assuming text data rather than numeric.

Pretty curious formula. It counts the same. Can you explain it, the part with "-10" at least?

 

[jasonb75]

The approximate match looks for the last row containing data. Then you subtract 10 for the rows at the top which are not included in the count (A1:A10).

 

[2Took]

The approximate match looks for the last row containing data. Then you subtract 10 for the rows at the top which are not included in the count (A1:A10).

That makes sense. And if there would likely be empty rows in the middle of the range, what formula would you go with?

 

[2Took]

The approximate match looks for the last row containing data. Then you subtract 10 for the rows at the top which are not included in the count (A1:A10).

1. That makes sense. And if there would likely be empty rows in the middle of the range, what formula would you go with?
2. Tried to incorporate your solution to =SUBTOTAL(103,A11:A5000) with =SUBTOTAL(103,(MATCH("zzz",A:A)-10)) and =MATCH("zzz",SUBTOTAL(103,A:A)), but got below XL responses respectively. What is the way to incorporate it correctly?

 

[jasonb75]

With empty rows, just go with what you had originally on an oversize range.

Using COUNTA on ~1million rows will take less processing effort than trying to resize the range that the formula uses.

 

[2Took]

With empty rows, just go with what you had originally on an oversize range.

Using COUNTA on ~1million rows will take less processing effort than trying to resize the range that the formula uses.

Tried to incorporate your solution to =SUBTOTAL(103,A11:A5000) with =SUBTOTAL(103,(MATCH("zzz",A:A)-10)) and =MATCH("zzz",SUBTOTAL(103,A:A)), but got below XL responses respectively.
What is the way to incorporate it correctly?

 

[jasonb75]

There is no correct way, i did say in my previous reply what you need to do and it didn't involve subtotal or match.