10

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.

| improve this question | |
18

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 sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    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).

I made a test script which you can take a look at: https://spreadsheets.google.com/ccc?key=0Ao3E95ou1MgbdG45blRvWlVaYkxFY01PQUhvZkZQcEE&hl=en&authkey=CPyZqxk

| improve this answer | |
  • This is exactly what I'm looking for, but each time the script runs it adds onto the value that is currently in the cell, so I'm getting more than the sum of the sheets in the cell each time it runs. How can I make it delete/replace the current value and just add the sum of the other sheets? – Keith Sep 23 '14 at 4:33
  • 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
  • 1
    If the values of the other cells change, the returned value is not being updated because a custom function only runs when its parameters changes. – Rubén Feb 4 '16 at 10:35
0

If anyone stumbles upon this question and has the same problem there's a 3D Reference Add-On for Google Sheets that lets you define ranges through multiple sheets and can be used with native functions like =SUM()

Some explanation on the authors page

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy