Monday, July 22, 2013

Google Spreadsheet Sum of Cells on different Sheets

Question:

( by Ashtine1 )


Hello, I started creating a Google sheets spreadsheet with many sheets. I want to create a range formula in the cell E8 of the first sheet named "TOTALS", that adds the value of the cell  G20 of all the other sheets. I don't want to put in each sheet name because that would make the string too long. Just create a range for the same cell from the first sheet to the last sheet. I know how to do it in Excel, I use:  =SUM('SCH001:SCH0050'!G20). SCH001:SCH0050 is the range from sheet one to sheet 50, and G20 is the cell with the data on every sheet, and i place this formula on the TOTALS sheet, cell E8. Works fine in Excel, However, the same formula wont work in Google sheets. it will be great if someone can help me.



I have made the spread sheet accessable to the public for edit.The name of it is "Physical Security Data Sheets"
this is what i want to do.
On the first sheet named "TOTALS". you will see $20,500.00 in cell E8. this must be the result obtained when the data in cell G20 of sheets SCH001, SCH002, and SCH003 are added together. In this example i have given you only three(3) sheets in in reality i will have about 54 sheets. So putting each sheet name into the equation is not an option because the string will be too long.
It also has to be applied to all of the other amounts in col E with there corresponding cells on each sheet. I can follow whatever you do for E8 and repeat it for the others.

So, cell E8 of the "TOTALS" sheet must show the sum of the data in cell G20, for all of the sheets, SCH001, SCH002, and SCH003. and the result should be $20,500.00.

I hope that this is clear enough.
Is there anything else that I need to give you for you to find the spreadsheed in google?

Thanks Much,
Robert.

Solution:

Have a look at the following screenshot of Sheet "TOTALS":



I have the following formula in Cell E8:

=KTotal("SCH";"G20";3)

the above formula is a custom function that I have written and inserted in Script.


NOTE: Formula should be in the following format:
=KTotal("SheetName";"CellName";NumberOfSheets)

That is, Sheet Name should be first parameter of the function and that should be in double quotes. And sheet name should be only the initial letters that are going to be same in all sheets, for the above example it is "SCH" and after that it is 3 digit numbers so 001, 002, 003 ... will be auto added to it in script. And in the script I have set that sheet name will have three digits at the end (suffix). I you want it more than three or less then you need to edit script.

Cell Name should be second parameter of the function and that should also be in double quotes.

And number of sheets should be the third and last parameter of this function and it should be without double quotes.


Have a look at the following script code:

///////////////////////////////////////

function KTotal(sheetname,cell,numberOfSheets)
{
  var sum=0,sname="";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
    
  for(var i=1;i<=numberOfSheets;i++) {
    if(i<10) sname=sheetname+"00"+i;
    else if(i<100) sname=sheetname+"0"+i;
    else if(i<1000) sname=sheetname+i;
    
    sum += ss.getSheetByName(sname).getRange(cell).getValue();
  }
  return sum;

}

///////////////////////////////////////

put the above code in your script editor, and then you can use it directly in your Google Spreadsheet or you can use its functionality in the Google Apps Script.



And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

8 comments:

  1. Kishan, Thanks. I put in the script code on a new tab i named "Totals" because there was a tab there already named "VMerge V2.11.gs", and i did not know if i could delete that. I also entered the formula in cell E8. However although it gave me the desired value, when i changed the value in cell G20 of sheet "SCH002", the value in E8 of the "TOTALS" sheet did not change at all.
    hope you can help.
    Thanks.
    Robert

    ReplyDelete
  2. I've been searching for something like this for a while and this was very helpful. I did end up changing some things around (the name included) since my naming convention was different. The way I have my pages set up is by year, so I changed it to reflect that:

    function SheetRangeSum(firstsheet,cell,lastsheet)
    { var sum=0,sname="";
    var ss = SpreadsheetApp.getActiveSpreadsheet();

    for(var i=firstsheet;i<=lastsheet;i++) {
    sname=i;
    sum += ss.getSheetByName(sname).getRange(cell).getValue();
    }
    return sum;
    }

    and the call looks like this: =SheetRangeSum(2011,"B3",2014)

    ReplyDelete
  3. I've tried both examples and get "Circular Dependency Detected." What am I doing wrong?

    ReplyDelete
  4. Made a couple of changes, now get: TypeError: Cannot call method "getRange" of null. (line 7).

    Same message, but line 11 in original example

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. I know this was a couple years ago now, but I have this same problem. Did you ever figure it out?

      Delete
    3. Tab name was the problem for me, I put month names as tab names, but then I put M001,M002 and so on as names. Try this!
      Also as I saw above it written =KTotal("SCH";"G20";3) with a ; in the middle however in my google sheets it's a comma(,) like this =KTotal("SCH","G20",3) couldn't figure but works.
      However after 26 Sums it didn't work and is showing random numbers.

      Delete
  5. This is great! Took a bit to setup, though. When I tried copying the KTotal formula for the next cells by dragging, it did not automatically change the row number. Any way around this?

    ReplyDelete