0

I have multiple sheets in my Google Spreadsheet. Let's suppose that they are named "1", "2", ... "n". I would like to sum the contents of the B4 cells in all of these sheets. Is it possible to specify a range of sheets, or do I need to meticulously include a reference to each sheet?

2
  • 1
    It can be done with a custom formula. There may be several precedents but a quick "Google" revealed A Google Custom Function to sum up the values of a specific cell across multiple sheets. I've tested it and it works. It requires a comma-separated string with the sheets to sum up, but this could easily be generated within the function. I'm reticent to provide a link only but perhaps you have seen this and discounted it. If you want help with this, let me know.
    – Tedinoz
    Apr 18 '21 at 11:42
  • I had not found this. I guess I was hoping for something in the native google sheets language, but this works - and its very existence causes me to suspect that a native google sheets language solution is not possible. If you were to somehow formulate this as an answer I would gladly accept it.
    – Him
    Apr 18 '21 at 17:15
0

You have a Spreadsheet containing n sheets (named "1", "2", "3", "4", "5" and so on) which have identical formatting. You want an efficient way to sum the values of a given cell (B4) from every sheet.

An option is to use a custom function such as sum-sheets-cell.js on Github.

Since your sheets are named in numerical sequence, I have modified the function to automatically insert the sheet names and cell reference.


snapshot


/**
 * Variables.
 *
 * You should not have to alter anything in the function,
 * only these variables.
 */
// The name of the sheet the dynamic date exists on.
var dateSheet = 'Sheet8';
// The cell of the sheet defined above in the "dateSheet" variable,
// that the script should update the date in.
var dateCell = 'A1';
// Name of the "Refresh" menu tab and link.
var refreshName = 'Refresh';


/**
 * Add a "Refresh" menu tab and link.
 *
 * We need this to have a way to trigger a callback that will update
 * a specific cell.
 * You can read more about it in the DocBlock for refreshLastUpdate().
 *
 * @See http://stackoverflow.com/questions/17341399/refresh-data-retrieved-by-a-custom-function-in-google-spreadsheet
 */
function onOpen() {
  // Define the menu item.
  // The refreshName variable is defined at the top of the document.
  var entries = [{
    name : refreshName,
    functionName : "refreshLastUpdate"
  }];

  // Add the menu item.
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  sheet.addMenu(refreshName, entries);
};

/**
 * Update a cell with the current date+time.
 *
 * We do this to work around the lack of references to real fields.
 * Custom functions doesn't update when a field it fetches values from
 * is updated, so we needed another way to recalculate custom functions.
 *
 * @See http://stackoverflow.com/questions/17341399/refresh-data-retrieved-by-a-custom-function-in-google-spreadsheet
 */
function refreshLastUpdate() {
  // Update the date cell with the current time and date.
  // The "dateSheet" and "dateCell" variables are defined outside of this function, at the top of the file.
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dateSheet).getRange(dateCell).setValue(new Date().toTimeString());
}

/**
 * Get the sum of a cell from multiple sheets.
 *
 * @param {string} Sheets - Reference a cell or write a comma-separated string with the sheets to sum up.
 * @param {string} Range - The range we want to fetch values from. Ex: "A1" or "A1:A6".
 * @param {string} Datetime - A dynamic date string.
 *
 * @return {number} Sum - The sum of the field across multiple sheets. 
 * @customfunction
 */
function SUMSHEETS(sheets, range, datetime) {
  // Dummy data - We need these when we test functionality inside the editor.
   //var sheets = '1,2,3,4,5,6', range = 'B4';

  // Get the active Spreadsheet and sheets.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var allSheets = ss.getSheets();
  
  // START: code added to automatically get sheet names and cell reference
    var summarysheetname = "summary2";
    var summary = ss.getSheetByName(summarysheetname);
    var startsheet = summary.getRange("B4").getValue();
    var lastsheet = summary.getRange("B5").getValue();
    var range = summary.getRange("B6").getValue();
    var sheetarray = []
    for (var i=startsheet;i<lastsheet+1;i++){
      sheetarray.push(i);
    }
    var sheets = sheetarray.join(","); 
    //Logger.log(sheets)
  // END: code added to automatically get sheet names and cell reference


  // Parse sheets-string to an array of sheet-names.
  var sheets = sheets.split(',');

  // Prepare all sheet-names to a new array.
  // We do this to get the actual name of all the sheets in the Spreadsheet
  // and format them to lowercase so we're case-insensitive.
  var sheetNames = [];
  for (i = 0; i < allSheets.length; i++) {
    sheetNames.push(allSheets[i].getName().toLowerCase());
  }

  // Loop through each of the user-provided sheet-names and find a match
  // with the active Spreadsheet sheets.
  // We do this to fetch the sheet-object with all values etc inside.
  // When we have found a match, we add the values of the user-provided range
  // and sum the values of the range. - This sum will then be added to the
  // total sum that we return at the end of the function.
  var totalSum = 0;
  for (i = 0; i < sheets.length; i++) {
    // Get the index of the sheet-object.
    var sheetIndex = sheetNames.indexOf(sheets[i].toLowerCase());

    // If the sheet exists (the index is bigger than 0. False will result in -1).
    if (sheetIndex > 0) {
      // Get the current sheet.
      var sheet = allSheets[sheetIndex];
      // Get the values of the provided range.
      var values = sheet.getRange(range).getValues();
      // Get the sum of the range.
      var sum = eval(values.join('+'));

      // Add the sum of the range to the total sum.
      totalSum = totalSum + sum;
    }
  }

  // Return the total sum of the range across the sheets.
  return totalSum;
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.