Google Sheets – How to Sum Throughout Different Sheets

google sheetsgoogle-apps-script

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

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

Best Answer

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

Related Topic