CODEWITHSUNDEEP
excelgoogle-sheets
Argyll
Argyll

Reputation: 9875

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

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?

Upvotes: 5

Views: 9018

Answers (5)

umlimo
umlimo

Reputation: 1

The match formula will search a range for the first instance of a given value, but cannot match a blank cell, so we need to provide a range with some value it can match. We can do this with an arrayformula, which applies its inner formula to every cell of the range. In this case, we can use the isblank formula to return true for every blank cell, then match to true: =match(true,arrayformula(isblank(A2:A)),0)

Upvotes: 0

Ashwin
Ashwin

Reputation: 21

Instead of:

sum(A2:index(match(TRUE,isblank(A2:A))))

just add arrayformula for isblank so that it returns an array internally:

sum(A2:index(match(TRUE,ArrayFormula(isblank(A2:A)))))

Upvotes: 2

Breno Araujo
Breno Araujo

Reputation: 71

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.

Upvotes: 7

MikeD
MikeD

Reputation: 8941

Asuming column headers in row 1 followed by one blank row, sample data in column A, interrupted by 1 or more blank cells ...

  1. find the top row of each group in column B

    B3: =AND(NOT(ISBLANK(A3)),ISBLANK(A2))

  2. build a group value in column C

    C3: =IF(AND(NOT(ISBLANK(A3)),ISBLANK(A2)),C2+1,C2)

  3. build a conditional sum for each group in column D

    D3: =sumif($C$3:$C$25,C3,$A$3:$A$25)

  4. combine all above in column E

    E3: =if(AND(NOT(ISBLANK(A3)),ISBLANK(A2)),sumif($C$3:$C$25,C3,$A$3:$A$25),"")

After each step copy down the formula from row 3 until the end of the table. Mind the absolute addressing for the SUMIF() function.

After all this works to your satisfaction, you may throw away columns B and D, they are just for illustration how we build it up and not used in the final formula.

enter image description here

Upvotes: 2

JPV
JPV

Reputation: 27262

In any cell you need the sum function, the range would start one row below the formula and end when the first blank cell is found ? If so this should work:

=sum(indirect("A"&row()+1&":A"&MIN(ArrayFormula(filter(row(indirect("A"&row()+1&":A")); indirect("A"&row()+1&":A")="")))))

This formula can simply be copy pasted.

Upvotes: 1

Related Questions