Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question
    
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
add comment

2 Answers 2

up vote 1 down vote accepted

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))
share|improve this answer
    
You must use Google Apps Script as a last resort. –  Jacob Jan Tuinstra Dec 13 '12 at 9:18
add comment

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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