Sum cells from a selection of sheets in a Google Sheets Workbook

Question

I have created a workbook for tracking inter-store item transfers. There are pages for every day of the month. I want to make a totals page to tally the total transfers on a separate sheet. (There are other pages in the workbook that I do not want added to the total.)

I am trying to make a script that will take the value of cell A1 on sheets 1 through 31 and put the sum on sheet 32 in cell A1 and likewise for every other cell.

Right now I am using a google sheets add-on called 'Custom Count and Sum' which lets me add up all the pages together like I want but the page names are passed as strings so you cant copy and paste the formula across cells. I found a workaround for this by using address(row(),column(),4,true) to reference the cell position so I don't have to manually type every cell in. Now I am trying to make it so the values will update without me having to reenter the formula. I cant do this using the add-on function I installed because as far as I can tell I need to pass another argument but I cant do this in the add on formula. I am trying to write my own script and I have tried to use the information in the question below but I am not having any success.

Google Sheets Sum values from ALL sheets in a workbook

Any help would be appreciated

Could you share your sheet? Does your sheet only have numbers? Are you open to a Apps Script solution — Raserhin
docs.google.com/spreadsheets/d/… I am open to using scripts, I already have a few I made in the workbook. I apologize in advance for my amateur scripting skills. — Clayton
Which one is the data you want to add up? There are a ton of cells that are not numeric (blank or characters). Maybe an example (or screenshot) of what you want? — Raserhin
Customers enter values into the cells associated with an item and their store. So a range like D7:I18 would have values entered for that days order. I entered some example numbers on sheet 1 in the example workbook I sent you. I want to total all of the cells that are associated with a store and an item. I am looking for a script that would be flexible if I need to add or remove items from the guide in the future. — Clayton
I'm stil not sure what are the ranges that you want to get the sum of? Is the range of D7:I18? You say you want a script per flexebility, what are your constraint to make possible to determine the range to sum? How would your change grow/change? — Raserhin

Raserhin Raserhin · Accepted Answer · 2020-01-23T11:10:26

Let's see if this workaround works for you.

In this case if you want a function to get all the cells and sum, you should try to match the sheets you want to get your values from. In your case you could look for all the values that are a number.

/**
 * Sum all the values in the cells/range of all the sheets that match the
 * regular expressión `(\d)+`
 *
 * @param {string} A1Notation - Range in A1Notation that would be sum for all the cells  
 */
function SUM_SHEETS(A1Notation){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();

  var regEx = /\b\d+\b/; // The regEx that will match the sheets

  var result = 0;
  for(var i=0; i < sheets.length; i++){
    var sheet = sheets[i];

    // Check that your sheet is matching your regular Expression
    if( regEx.test(sheet.getName())){

      var range = sheet.getRange(A1Notation);
      var values = range.getValues();

      // Iterate through all the range from all the current sheet
      for(var j=0; j < values.length; j++){
        for(var k=0; k < values[j].length; k++){
          var value = values[j][k];

          // Only sum when the cell is a number
          if (typeof value == "number"){
            result += value;
          }
        }
      }
    }
  }

  return result;
}

Take into consideration that the input parameter is a string representing your range so you can iterate through the sheets.

Also care with the sheets, in this workaround I'm using a regExp that will sum all the values that are a number (only digits).

Sum cells from a selection of sheets in a Google Sheets Workbook

3 Answers