Countif Indirect Google Sheets

Question

I am trying to write a formula that counts the number of times the number 1 appears in cell F1 of all my sheets. My sheets have varying names (18-0100, 18-0101, 18-0102...). I tried the following formula:

=COUNTIF(INDIRECT("'"&"'!F1"),"=1")

It acts unpredictably. It will only return 1 even if it should be more than 1. And when I try to start trying to count 2 instead of 1 it returns 0 and not the correct number.

What am I doing wrong?

Answer 1

Your formula counts only the current sheet.

To get them all you need to enter all sheet names:

The formula for each sheet is:

=(INDIRECT("'"& sheet_name &"'!F1")=1)*1

Answer 2

You can leverage a Google Apps Script to pull this off as well.

From your spreadsheet, go to Tools > Script Editor. Add the code below, then save. The function saved there will be accessible by all other Google apps, including your spreadsheet. You can then use the name of the function in your spreadsheet like you would with any other formula.

function OccurrencesInSheetNames(str) {
  var c = 0;
  var regExp = new RegExp('[' + str + ']', 'g');
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var sheetnames = [];
  for (var i=0; i<sheets.length; i++) {
    sheetnames.push([sheets[i].getName()]);
  }
  for (var i=0; i<sheetnames.length; i++) {
    var name = sheetnames[i].toString();
    var count = (name.match(regExp) || []).length;
    c += count;
  }
  return c;
}

Now in your cell F1, place the following formula:

=OccurrencesInSheetNames(TEXT(1,0))

You can also replace the 1 in the above formula with a cell reference, if that works better for your needs. (e.g. =OccurrencesInSheetNames(TEXT(C5,0)) if you want to search through your sheet names for the integer number found in cell C5.)

Here's a demo spreadsheet for you to try out.