Sum column until first empty cell

Question

i have the following table:

A1 - 25

A2 - 26

A3 - 27

A4 - BLANK

A5 - 30

A6 - 23

A7 - BLANK

A8 - 24

In B1, i want the following - Starting from A1, sum up the entries until the first blank cell is encountered. In this case, it would be 25+26+27 = 78.

I have looked at multiple answers for hours and tried tweaking them, but nothing is working. Any help is appreciated (Also many things do not make sense, the function isblank(a1:a10) is going to return true or false, then how does arrayformula(isblank(a1:a10)) suddenly convert it to an array, since isblank is just returning a boolean?)

Answer 1

Here's another way you can do it:

=sum(indirect("A1:A"&filter(row(A:A),A:A="")))

Answer 2

try:

=FLATTEN(INDEX(QUERY(; "select "&SUBSTITUTE(JOIN("+"; 
 IF(INDIRECT("A1:A"&MAX(ROW(A1:A)*(A1:A<>"")))=""; 
 ","; A1:A)); "+,+"; ",")); 2))

Answer 3

Here's a couple of methods for it and a spreadsheet showing them both.

https://docs.google.com/spreadsheets/d/1rkLarQC6NQ4HdGa38X3-rPoAW0A2-USvImFimlelhZM/edit#gid=0

Method 1: use MATCH to find the row of the first blank row, then construct a reference with INDIRECT to pass to SUM:

=sum(indirect("a1:a" & match("~~", arrayformula("~" & A1:A10 & "~"), 0) - 1))

Reformatted:

=sum(
  indirect(
    "a1:a" & 
    match(
      "~~", 
      arrayformula("~" & A1:A10 & "~"), 
      0
    ) - 1
  )
)

The only tricky thing here is that MATCH returns an error if you just pass it "" to look for, so I use ARRAYFORMULA to wrap the A1:A10 range in a delimiter (~ in this case, but that was arbitrary) and then look for ~~ in the array. That returns me row 4, and so I use indirect to construct a reference to A1:A3 and pass that to sum.

---

similar to ztiaa's method, but inferior. He filters the ROW() results directly, and uses A:A as the filter argument. Both are superior to my use of ISBLANK etc passed to FILTER

Second, the same idea (find the number of the first empty row and construct a reference to pass to INDIRECT):

=sum(indirect("a1:a" & filter(ARRAYFORMULA(isblank(A2:A11)*row(A2:A11)), ARRAYFORMULA(isblank(A2:A11)*row(A2:A11))<>0)-1))

Reformatted for easier reading:

=sum(
  indirect(
    "a1:a" & 
    filter(
      ARRAYFORMULA(isblank(A1:A10)*row(A1:A10)), 
      ARRAYFORMULA(isblank(A1:A10)*row(A1:A10))<>0
    ) - 1, 
  )
)

So I use ISBLANK(A1:A10) to get an array of booleans indicating which rows are empty, then multiply that by ROW(A1:A10) which will return an array containing all the row numbers for the range, all inside of ARRAYFORMULA.

ARRAYFORMULA(isblank(A1:A10)*row(A1:A10))

Using boolean values in the multiplication converts them to zeroes, so this will generate an array of either 0 (for non-blank rows) or a row number (for any blank rows). Then I take the same formula and use FILTER on it to remove all of the zeroes

      filter(
        ARRAYFORMULA(isblank(A1:A10)*row(A1:A10)), 
        ARRAYFORMULA(isblank(A1:A10)*row(A1:A10))<>0
      )

leaving an array containing the row numbers of each blank row. Since they are in order and Sheets lacks dynamic array handling, the return value will just be the first value instead of the array, and so we can pass that to INDIRECT to generate a reference to a range using that row number - 1 (since I want to have the range run from A1 to the row immediately preceding the first blank row):

  indirect(
    "a1:a" & 
    filter(
      ARRAYFORMULA(isblank(A1:A10)*row(A1:A10)), 
      ARRAYFORMULA(isblank(A1:A10)*row(A1:A10))<>0
    )-1
  )

and then as a final step wrap the whole thing in SUM to sum the values in the range you just used INDIRECT to create a reference to.

=sum(
  indirect(
    "a1:a" & 
    filter(
      ARRAYFORMULA(isblank(A1:A10)*row(A1:A10)), 
      ARRAYFORMULA(isblank(A1:A10)*row(A1:A10))<>0
    )-1
  )
)

[![enter image description here][1]][1]

Let me know if that works for you. I imagine there is a better way to do it. I'll keep thinking about it.

Answer 4

Few alternative

=SUM(QUERY(A:A,"select A limit " & MIN(FILTER(ROW(A:A),A:A=""))-1))

With INDEX() function

=SUM(INDEX(A:A,1):INDEX(A:A,min(filter(row(A:A),A:A=""))-1))