3

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?

  • Is there not way to use more columns or sheets? That way you can select entire columns, rather then specific ranges: SUM(B2:B) – Jacob Jan Tuinstra Dec 12 '12 at 9:47
  • It would be significantly less readable (for a certain kind of datasets), IMO. – Alexander Gladysh Dec 12 '12 at 11:14
  • Btw, if B2 is blank, then it simply would not account to the summation. I guess what you're looking for is this: IF(SUM(B2:B4)="";"";SUM(B2:B4)) – Jacob Jan Tuinstra Dec 12 '12 at 11:16
  • Another option is to set the summation in C5. If you insert a row above or below, then all formula's will be adjusted accordingly. – Jacob Jan Tuinstra Dec 12 '12 at 11:21
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))
  • You must use Google Apps Script as a last resort. – Jacob Jan Tuinstra Dec 13 '12 at 9:18
3

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.

  • I remember using a workaround for a cache problem somewhere. I think I referenced a field that contained a current timestamp... – Alexander Gladysh Dec 12 '12 at 23:38
  • Aha, that was for ImportXML. Not sure if that trick will work here. Will try. It was something like =ImportXML("http://example.com/data.xml&t="&System!B1; "/foo/@bar") and =year(now())&"-"&month(now())&"-"&day(now())&"-"&hour(now())&":"&minute(now()) in &System!B1; – Alexander Gladysh Dec 12 '12 at 23:41
1

I had the same question as this thread, sum rows until a blank cell and display in the next column. I found this answer on another site by user JPV. I modified it for the original question:

=sum(indirect("B"&row()&":B"&MIN(ArrayFormula(filter(row(indirect("B"&row()&":B")); indirect("B"&row()&":B")="")))))

For my purposes, I enclosed that formula into an IF formula so that only the totals are displayed in the first row of values, and blank until the next group of rows.

0

This answer is based on William Jackson's fine answer, but solves the need to update the function when you change the data.

Create the function as he instructs, but with an additional parameter dummy:

function sum_until_blank(cell_description, dummy) {
  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());
}

When you use the function, include a second parameter that updates whenever you want the sum update. In this case a sum of all values in column B is suitable, so:

=sum_until_blank("B2", sum(B$1:B$99999))

This works because custom functions' value is cached unless one of their inputs has changed.

0

Several people have already answered this question, but I'd like to add an alternative that you might find both simpler and easier to use. Since you may pass a range of cells values to a Google Script function, you can just look at those values instead of reaching back and inspecting cells, like this:

function sum_until_blank(array) {
  var sum = 0;
outer:
  for (var i in array) {
    for (var j in array[i]) {
      if (array[i][j] === '')
        break outer;
      sum += array[i][j]
    }
  }
  return sum;
}

Call it with a range that starts with the first value and extends far beyond the expected end, e.g:

=sum_until_blank(b2:b99)

Summation will stop at the first blank value within the range and the range references will automatically update if you insert, delete, or move the cells on your spreadsheet.

Note that all ranges will be converted to two-dimensional arrays in Google Script, so the vertical B2:B99 range will be received as [[B2], [B3], [B4], ...] whereas e.g. a horizontal B2:ZZ2 range would be seen as [[B2, C2, D3, ...]]. That's why we need to iterate over both dimensions.

Since you can return arrays too, you can easily generalize this to work with any Sheet function that takes a range as an argument.

function until_blank(array) {
  var i, j;
outer:
  for (i in array) {
    for (j in array[i]) {
      if (array[i][j] === '')
        break outer;
    }
  }
  if (i > 0) {
    // A horizontal range
    return array.slice(0, i);
  } else {
    // A vertical range
    return [array[0].slice(0, j)];
  }
}

This allows you to do e.g:

=sum(until_blank(b2:b99))
=product(until_blank(b2:b99))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.