# In Google Spreadshets, how can you loop through all available sheets not knowing the total count?

I'm keeping stats for my daughter's baseball team. I have a sheet for each game, each one identical in structure. I want to create a summary sheet that totals numbers from the other sheets. How should I construct the formulas to reference N number of sheets?

Example:

``````=G1!C2+G2!C2+G3!C2
``````

I don't want to go back and change each formula when a new sheet is added.

Update: Searching some more at Google Help, I don't think this is supported. What I'm doing is referred to as a 3D range and though it is supported in Excel, it is not supported in Google Spreadsheet.

If you don't mind using a macro, you can do the following. Add this script to your spreadsheet (Tools -> Scripts -> Script editor):

``````function getTotalSum(cell) {
var sum = 0;
for (var i = 0; i < sheets.length ; i++ ) {
var sheet = sheets[i];
var val = sheet.getRange(cell).getValue();

if (typeof(val) == 'number') {
sum += val;
}
}

return sum;
}
``````

and use the formula =getTotalSum("A1"), where "A1" is the cell which you want to sum up in all the sheets (except the one you putting the formula in).

• Are you using the same cell position to do the summary? If so then it's probably adding that to the total. And then when you run it again, it adds the new sum to the old sum, etc etc. If you just put it in another cell it should work. If you'd prefer not to, you'd need to change the script to get the id of the current sheet, and then add a check to the if statement so it doesn't include that. First add above for loop: `var thissheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();` Then second change the if statement like so: `if (typeof(val) == 'number') && (i != thissheet)` – goodship11 Jul 26 '15 at 18:33
• Oops, wrong syntax on the if statement. Use this: `if (typeof(val) == 'number' && i != thissheet)` – goodship11 Jul 26 '15 at 19:51