Summing values from multiple sheets with names specified in cells

Question

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.

Answer 1

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.

Answer 2

you could have done some manual row stacking like:

=SUM(COUNTIF(INDIRECT(A1&"!A:A"),"bananas"),
     COUNTIF(INDIRECT(A2&"!A:A"),"bananas"),
     COUNTIF(INDIRECT(A3&"!A:A"),"bananas"))

however, more user-friendly would be to have one more column which will generate sub-strings for SUM based on the existence of sheet names in the column: https://dcs.ggl.com/spreadsheets/d/

where cell B2: =IF(A3<>"",COUNTIF(INDIRECT(A3&"!A:A"),INDIRECT("D1")),)

and cell D2 is: =SUM(B3:B)

Answer 3

The QUERY() function might help out here. It allows you to use a simple SQL query on top of one or many sheets. Here's an example based on OP's question:

=QUERY({Sheet1!A:B;Sheet2!A:B;Sheet3!A:B},"SELECT SUM(Col2) WHERE Col1='Bananas' label sum(Col2) ''")

I provided a query that strips out the output header by setting the output label of Col2 to nothing. If you need to retain the header label, you could just set it to whatever you want.