I have tried the following custom function successfully up to 170 sheets:
function getTotalSum(startrow, startcol) {
var ss = SpreadsheetApp.getActive();
var start = new Date();
var numSheets = ss.getNumSheets(), sum=0;
for(var k=1; k<numSheets; k++) {
var data = ss.getSheets()[k].getDataRange().getValues();
var value = data[startrow-1][startcol-1];
if(typeof(value) == 'number') {
sum += value;
}
}
return sum;
}
Going beyond the 170, problems ariase:

Therefore I created the code below that can be used for at least 750 sheets (but not to be used as a custom function).
Code
// global
var ss = SpreadsheetApp.getActive();
function onOpen() {
ss.addMenu("Sum", [{name: "Go !!", functionName: "getSum"}]);
}
function getSum() {
// get user input
var cell = Browser.inputBox('Enter A1 notation', 'like B21',
Browser.Buttons.OK_CANCEL);
// retrieve cell reference from a sheet and get row and col index
var aCell = ss.getActiveSheet().getRange(cell.toString());
var row = aCell.getRow()-1, col = aCell.getColumn()-1;
// get number of sheets and set sum to zero
var numSheets = ss.getNumSheets(), sum=0;
// iterate through sheets, starting from second sheet
for(var k=1; k<numSheets; k++) {
// bulk load sheet at once
var data = ss.getSheets()[k].getDataRange().getValues();
// get value from array
var value = data[row][col];
// valid value
if(typeof(value) == 'number') {
sum += value;
}
}
// return value to active cell
ss.getActiveCell().setValue(sum);
}
Explained
The custom function isn't really meant to be used this extensively. Oddly, the custom function runs well within the 5 minutes of execution time for the 170 sheets (30s). The usage of .getDataRange().getValues()
is slightly advantageous over the .getValue()
(appr. 30s for 750 sheets).
Example
I've created an example file for you: 750 sheets
Create a copy and look for the extra pieces of code I added.