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.

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

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

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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

share|improve this answer

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.