Sum until empty line function in Google Spreadsheet

Question

I'm using this data layout a lot:

   A      B     C
 1 Name1  Count Total
 2 A      1     =SUM(B2:B4)
 3 B      2
 4 C      1
 5
 6 Name2  Count Total
 7 D      1     =SUM(B7:B8)
 8 E      2

It is useful to present data to viewer, but it is ugly to maintain. If you have to add an entry to a given category, you have to edit corresponding SUM manually.

I'd like to be able to write something like =SUM_UNTIL_BLANK(B2).

Is it possible with Google Spreadsheets?

Answer 1

Set the summation to cell C5. If you insert a row above or below, then all formula's will be adjusted accordingly. You can add this to the cell as well:

IF(SUM(B2:B4)="";"";SUM(B2:B4))

Answer 2

Let me tell you up front that you may not be happy with this solution. It is probably just as annoying as having to update the function whenever you change the data, but annoying in different ways.

You can write a custom function to do this. From the Tools menu, select Script editor.... Click the Close button on the welcome screen, erase any code that is already there, and paste this in:

function sum_until_blank(cell_description) {
  sheet = SpreadsheetApp.getActiveSheet();
  var this_cell = sheet.getRange(cell_description);

  if (this_cell.isBlank()) {
    return 0;
  }

  var this_value = this_cell.getValue();
  if (typeof this_value != "number") {
    return 0;
  }

  next_cell = sheet.getRange(this_cell.getRow() + 1, this_cell.getColumn());
  return this_value + sum_until_blank(next_cell.getA1Notation());
}

Save the script. It doesn't matter what you name the project. You can now use this function in your spreadsheet like this: =sum_until_blank("B2"). Take note of the following limitations:

• The cell reference must be quoted. =sum_until_blank("B2") will work but =sum_until_blank(B2) will not.

• Custom functions are slow.

• Custom functions in Google Sheets have an annoying caching behavior. After you make a change in the input column, you may need to close and reopen the spreadsheet, or even wait a while (I'm not sure how long) before the custom function will recalculate.

  You can get around the caching by adding a dummy parameter to the function call (e.g. =sum_until_blank("B2", 0)) and changing the dummy parameter each time you want the function to recalculate, but this is of course no better than using your original function and changing the input range as necessary.