# How do I Sum Thoughout Different Sheets

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

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

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"}];
}
``````

## 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

• 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? Commented Sep 23, 2014 at 4:39