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, Jan 20 '20 at 11:12
https://docs.google.com/spreadsheets/d/1HPsf3aHeudtra9TGZbNFrYJxaieA5_vorS3nst5iE-8/edit?usp=sharing 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, Jan 20 '20 at 18:39
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, Jan 21 '20 at 08:59
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, Jan 21 '20 at 17:33
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, Jan 22 '20 at 16:31
Ideally I would have a function that I could enter into a cell. That function would go through the numbered sheets and add the values of that cell together. Something like =sum('1'!D7:'5'!D7). This would then return the total sum of every value entered into cell D7 in the specified sheets. — Clayton, Jan 22 '20 at 17:21

score 0 · Answer 1 · answered Jan 18 '20 at 16:15

Generally it's best to collect all your data for every day, for every month and through all the years all in one tab. each item on it's own line with a date, like a database.

It's MUCH easier to disaggregate data that's altogether on one tab into different tabs for summing/counting/analysis than it is to aggregate data that is in different tabs onto one.

score 0 · Accepted Answer · answered Jan 23 '20 at 11:10

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).

Thank you very much. This seems to be giving me the results I desire. I guess I need to learn how regEx works now. Thanks again. — Clayton, Jan 23 '20 at 20:05
I don't know If you noticed but you can sum ranges to if you want. Meaning that instead of `A1` you could write `A1:B4`, and that would sum all the numbers in all that cells. — Raserhin, Jan 24 '20 at 08:01

score 0 · Answer 3 · edited Mar 06 '21 at 09:19

When using =SUM_SHEETS(A1Annotation), what I did to make this work (for the 13 rows I needed), was typing in L1,L2,...,L13 in Column A that would reference the cells from my sheets that I wanted to sum with this function In the Formula Column (B) I replaced the L1 portion of SUM_SHEETS() with the Cell A1 (since I just typed the L1 in Cell A13).

Example: | Column A | Column B | |----------------------|---------------------| | L1 | =SUM_SHEETS(A13) | | L2 | =SUM_SHEETS(A14) |
| ... | |
| L13 | =SUM_SHEETS(A25) |

Instead of: | Column A | |--------------------| | =SUM_SHEETS("L1") |
| =SUM_SHEETS("L2") | | ... | | =SUM_SHEETS("L13") |

Lastly, to refresh when I wanted it to, I created a button and a macro to literally, delete the data in cells A13:A25 and then put them right back in.

function Refresh_Summary() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A13:A25').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getRange('A13').activate();
  spreadsheet.getCurrentCell().setValue('L1');
  spreadsheet.getRange('A14').activate();
  spreadsheet.getCurrentCell().setValue('L2');
  spreadsheet.getRange('A15').activate();
  spreadsheet.getCurrentCell().setValue('L3');
  spreadsheet.getRange('A16').activate();
  spreadsheet.getCurrentCell().setValue('L4');
  spreadsheet.getRange('A17').activate();
  spreadsheet.getCurrentCell().setValue('L5');
  spreadsheet.getRange('A18').activate();
  spreadsheet.getCurrentCell().setValue('L6');
  spreadsheet.getRange('A19').activate();
  spreadsheet.getCurrentCell().setValue('L7');
  spreadsheet.getRange('A20').activate();
  spreadsheet.getCurrentCell().setValue('L8');
  spreadsheet.getRange('A21').activate();
  spreadsheet.getCurrentCell().setValue('L9');
  spreadsheet.getRange('A22').activate();
  spreadsheet.getCurrentCell().setValue('L10');
  spreadsheet.getRange('A23').activate();
  spreadsheet.getCurrentCell().setValue('L11');
  spreadsheet.getRange('A24').activate();
  spreadsheet.getCurrentCell().setValue('L12');
  spreadsheet.getRange('A25').activate();
  spreadsheet.getCurrentCell().setValue('L13');
};

(a bit of a workaround when the SUM(First:Last!L1) would be way faster BUT it works)

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

3 Answers3