CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetssumgoogle-sheets-formula
S Asplin
S Asplin

Reputation: 31

Google Sheets Summing a cell over a changing number of sheets

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.

Upvotes: 2

Views: 605

Answers (1)

TheMaster
TheMaster

Reputation: 50452

You can do this with custom Apps Script function in Google sheets:

Script:

/**
 * @returns {Number} Sum of all values from startSheet to endSheet of particular cell range
 * @customfunction
 *
 * @param {String} startSheet name like "Sheet1"
 * @param {String} endSheet name (inclusive) like "Sheet3"
 * @param {String} cell range like "A2" or "A2:A5" or "A2:B5" for adjacents
 */
function SUMSHEETS(startSheet, endSheet, range) {
  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) => {
        let values = sh.getRange(range).getValues();
        values = values.flat(); // ranges like A2:A5 are 1D array of (numbers or empty strings), ranges like A2:B5 are 2D array so 2D -> 1D
        values.forEach(function(value) {
          next = Number(value);
          if (Number.isNaN(next)) {
            throw new Error("got non number to sum? " + value + " " + sh.getName() ); // else corrupts output to NaN forever
          }
          output += next;
        });
      },
      () => 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('Start or end sheet(s) not found!');
  return getOutput();
}

Usage:

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

Sums C6's from sheet "J Slater" to sheet to its right "Job Sheet".

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

Sum C6, C7, D6, D7 from sheet "J Slater" to "Job Sheet".

Upvotes: 4

Related Questions