11

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

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
1

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

sorry to be late to this thread, but I saw your question and I thought this might be helpful for you for future reference. You would have to update your formula, but only in one place. While I know that is not ideal, it would be helpful. It may also be possible to build something that can dynamically pull together this needed string to reference the sheets for the array formula. I am not sure if that would work or not.

In Google Sheets you can use the array formula to do this. NOTE: It is very important that the sheets do indeed have the exact same structure. Same columns, in the same position, in every sheet. I've done this numerous times with the query function. It is quite easy if you know how to write a simple select statement.

One really important element of this to keep in mind is that once you use a function to 'obscure' the data (importrange, arrays, or nesting queries are some examples) you must stop referring to the columns in your select statement as letters, and switch to a column reference format. So, column A becomes Col1, B = Col2, etc etc.

Here is an example you can use:

=ARRAYFORMULA(query({sheet1A:D;sheet2A:D;sheet3A:D},"select Col1,Col3 where Col4 is not null"))

Col4 would be a number field in this case. If it was a text field you could use the below - this is actually a real one I used in a spreadsheet, with the names of the sheet changed: =ARRAYFORMULA(query({'Sheet 1'!A2:Q;'Sheet 2'!A2:Q;'Sheet 3'!A2:Q;'Sheet 4'!A2:Q;Sheet5NoSpaces!A2:Q;'Sheet 6 - Some Addtl Desc'!A2:Q},"select * where Col1<>''"))

There are a plethora of ways you can calculate your stats using the query function as well, so for your use that might be great.

Check the [reference document][1] for more

If anyone knows if you can write something like:

=ARRAYFORMULA(query({[STRING FROM FUNCTION]},"select * where Col1<>''"))

That would be really great to know about! I am not at the level of writing scripts in Google Sheets yet.

Best of luck!

| improve this answer | |

Your Answer

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