Count values until the first empty cell

Question

What I looking for is how to count as many values in column, but I want it to stop counting as soon as it hits the first empty cell. I am trying to do it without using app script.

Example:

1
2
312
EMPTY
3123

Should return 3, if I simply use COUNTA(), it will return 4.

Any ideas?

Answer 1

If your "empty" cells are indeed BLANK then you can use the following:
=ArrayFormula(match(TRUE,ISBLANK(A1:A13),0)-1)
(as long as there is always an empty row between the sets of "Years"

• the ISBLANK(A1:A13) returns an array result {FALSE,FALSE,FALSE,TRUE,FALSE,...}
• the match() returns the POSITION or ROW of the first TRUE in that list : 4
• we then take away 1, for the empty row
• we have to run the whole thing as an array formula because we need ISBLANK() to work on each cell in turn.

if they contain text "EMPTY" then use:
=ArrayFormula(match(TRUE,if(A1:A13="EMPTY",TRUE,FALSE),0)-1)