Excel Formula: incorporate MATCH with SUBTOTAL to count number of rows

[2Took]

Instead of

=COUNTA(A11:A5000)

below formula automatically adjusts to however many rows with data, instead of limiting to 5000 rows, or putting a million rows:

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

Tried to incorporate same solution into

=SUBTOTAL(103,A11:A5000)

with

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

and

=MATCH("zzz",SUBTOTAL(103,A:A))

but those are not correct, per Excel.

> What is the way to incorporate it correctly?

 

[jdellasala]

How about this?

=COUNTA(FILTER(A:A, A:A="zzz"))

Hope that works for you!

 

[2Took]

How about this?

=COUNTA(FILTER(A:A, A:A="zzz"))

Hope that works for you!

no... my data starts at row 11, but even with adjusting to:

=COUNTA(FILTER(A11:A, A11:A="zzz"))

it results in 1, which is way way waay off

 

[jdellasala]

no... my data starts at row 11, but even with adjusting to:

=COUNTA(FILTER(A11:A, A11:A="zzz"))

it results in 1, which is way way waay off

A11:A is an invalid address. There's "zzz" between A1 and A11? If not, what's the problem with doing the whole column?

 

[2Took]

A11:A is an invalid address. There's "zzz" between A1 and A11? If not, what's the problem with doing the whole column?

There are other formulas that I need to keep in first few A rows. My data starts at row 11, so need the count specifically from A11 and down.