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:

http://igoogledrive.blogspot.com/p/paid-projects.html

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,

Kishan.

iGoogleDrive