Google-sheets – Summing values from multiple sheets with names specified in cells

google sheets

I'm attempting to sum unknown quantities of data that is contained in several sheets. The amount of sheets isn't known either. Thus far, I've simply been using the indirect command on a column with an ever-growing list of sheet names, and that's been working fine with referencing them individually: 

        A
1   Sheet Name 1
2   Sheet Name 2
3   Sheet Name 3
4   Sheet Name 4

Each of these sheets is formatted identically but contain different data. So one may look like:

      A          B
1   Apples       7
2   Oranges      8
3   Bananas      3
4   Grapes       5
5   Plums        1
6   Strawberries 8
7   Bananas      3
8   Grapes       5

What I need to do is iterate through my list of sheet names, run a countif on that data, and then sum all of the results from the various sheets together.

I've tried using:

=sum(arrayformula(countif(indirect(A:A&"!A:B"),"Bananas")))

However, that only returns the result for the first sheet that's named. So in this case I would only get a 2 back, while sheets 2-4 may each contain 10+ Bananas.

Best Answer

A spreadsheet formula cannot reference an undetermined number of sheets. The logic of "count the occurrences of this value in this range in all sheets" can be expressed in a custom function such as 

=countinsheets("A:B", "Bananas", 1)

where the function is

function countinsheets(range, value) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var result = 0;
  for (var i = 0; i < sheets.length; i++) {
    var data = sheets[i].getRange(range).getValues();
    for (var j = 0; j < data.length; j++) {
      for (var k = 0; k < data[0].length; k++) {
        if (data[j][k] == value) {
          result++;
        }
      }
    }
  }
  return result;
}

This finds all occurrences of "Bananas" in columns A:B, in all sheets, and returns their number. Note that the range "A:B" is passed as a string, which is necessary in this case, but has a major drawback.

The drawback is: the function value will not auto-update when the spreadsheet data is changed. One has to force a refresh by changing the third parameter of the function, which is a fake parameter introduced precisely for that reason. Change 1 to something else to make the function recalculate; it will not do so on its own.

Related Topic