Google Sheets Summing a cell over a changing number of sheets

Question

I am trying to convert a Spreadsheet from Excel to Google Sheets. Most formulas have worked as expected, with the exception of this formula:

=SUM('J Slater:Job Sheet'!C6)

I am using it to sum a specific cell over a range of sheets this formula allows sheets to be added between the range an also be added to the sum cell without any change to the formula. I can't find a way of replicating this in Google Sheets.

Answer 1

You can do this with custom functions in Google sheets:

Script:

/**
 * @returns {Number} Sum of all values from startSheet to endSheet
 * @customfunction
 *
 * @param {String=} startSheet
 * @param {String=} endSheet
 * @param {String=} range
 */
function SUMSHEETS(startSheet = 'Sheet1', endSheet = 'Sheet2', range = 'A2') {
  if ([...arguments].some(arg => typeof arg !== 'string' || arg === ''))
    throw new Error('All arguments must be non-empty strings');
  const [addValue, getOutput] = (() => {
    let output = 0;
    return [
      sh => {
        output += sh.getRange(range).getValue();
      },
      () => output,
    ];
  })();
  let toggle = 0;
  const end = SpreadsheetApp.getActive()
    .getSheets()
    .some(sheet => {
      if (toggle) addValue(sheet);
      switch (sheet.getName()) {
        case startSheet:
          toggle = 1;
          addValue(sheet);
          break;
        case endSheet:
          return true;
      }
    });
  if (!toggle || !end) throw new Error('Sheet(s) not found!');
  return getOutput();
}

Usage:

=SUMSHEETS("J Slater","Job Sheet","C6")