4

How do I create a reference to a defined interval in several sheets?

Something like this: =SUM(Sheet1:Sheet2!A1)

1

You can use the following script to get the sum from multiple sheets:

UPDATE
Because the script is only executed at an interval of 5 minutes, and an OnEdit trigger won't work, I had to revise the answer I gave in order for it to be practical. See revision history to notice the differences.

The first part

will set the spreadsheet and create an extra menu option, called Sum:

var ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen() {
  var menu = [{name: "get sum", functionName: "sumSheets"}];
  ss.addMenu("Sum", menu);  
}

The second part

will ask for a starting position and collect the total number of sheets and will determine the position of the Result sheet (not sheet ID but the absolute position). After which it will open each consecutive sheet starting from the first parameter (zero based !!), to gather the values and add them to the sum, till it hits the Result sheet. At the end, the total sum is added to any active cell:

function sumSheets() {
  var pos = Browser.inputBox("Where do you want to start (zero based)?"); 
  var sh = ss.getNumSheets();

  for(var k=0; k<sh; k++) {
    if(ss.getSheets()[k].getName() == "Result") {
      var ref = k;
    }
  }

  var sum=0;
  for(var i=pos; i<ref; i++) {
    this.i = ss.getSheets()[i];
    var data = this.i.getRange(2, 1, this.i.getLastRow(), 1).getValues();
    for(var j=0, len=data.length; j<len; j++) {
      sum += Number(data[j]);
    }    
  }

  ss.getActiveCell().setValue(sum); 

  return;
}

To trigger the calculation, press get sum from the menu option Sum and the result will be added.

See example file I've prepared: SUM throughout multiple sheets

| improve this answer | |
  • When the script runs I tell it to start at 0 and it gives me a #NUM! error. If I give it a sheet name or cell it returns with "0." Am I just have your code above in the .gs file and I know it's running because it prompts the input box. Am I missing something or does the script need to be modified in some way? – Keith Sep 23 '14 at 4:39

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.