How to know the first blank cell in a column in Excel or Google Spreadsheet using only native formulas

Question

Suppose A2,A3,A4 are filled. A5,A6 are empty. A7-10 are filled.

Is there a way to know that A5 is the first blank cell after A2?

For example,

A1 | =sum(...)
A2 | 3
A3 | 4
A4 | 1
A5 |  
A6 | =sum(...)  
A7 | 2
A8 | 5
A9 | 7
A10| 10

What one formula may we use to replace "..." in order to sum the cells below until the next blank cell? ie. Can a formula on A1 for sum(A2:A4) be re-used on A6 for sum(A7:A10) by just copy-paste?

Here is my story of failed attempts.

So the starting row is easy to get. Just put in A2. For the end row, Google Spreadsheet doesn't equate "" with empty cell. So sum(A2:index(match("",A2:A))) will not work.

isblank() with an array input will give a single output FALSE regardless of the content. So sum(A2:index(match(TRUE,isblank(A2:A)))) won't work.

What can I do to make it work?

Answer 1

in order to find the first empty cell with MATCH you can do the following

=MATCH("@",ARRAYFORMULA(A2:A&"@"),0)

This is needed because MATCH doesn't work with blank cells, as you found out.