Search code examples
google-sheetsgoogle-sheets-formulagoogle-docsgoogle-docs-api

How to count (Search) for specific text across multiple sheets via Google Docs?

Currently I'm using the following formula to search and count the number of times a given text is used within a given cell:

=COUNTIF(Sheet1!G3:G1151, "COMPLETE")

Any ideas how I can use the same formula against multiple sheets?

Something like the following:

=COUNTIF(Sheet1, Sheet2!G3:G1151, "COMPLETE")

Thanks for your help

Solution

  • In case there are many sheets you want to look for, and to avoid having a to repeat the formula many times for each sheet, you can use a custom function created in Google Apps Script instead. To achieve this, follow these steps:

    • In your spreadsheet, select Tools > Script editor to open a script bound to your file.
    • Copy this function in the script editor, and save the project:
    function COUNTMANYSHEETS(sheetNames, range, text) {
  sheetNames = sheetNames.split(',');
  var count = 0;
  sheetNames.forEach(function(sheetName) {
    var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
    var values = sheet.getRange(range).getValues();
    values.forEach(function(row) {
      row.forEach(function(cell) {
        if (cell.indexOf(text) !== -1) count++;
      });
    });
  });
  return count;
};
    • Now, if you go back to your spreadsheet, you can use this function just as you would do with any other function. You just have to provide a string with all the sheet names, separated by a separator specified in the code (in this sample, a comma), another one with the range you want to look for, and the text you want to look for, as you can see here, for example:

    =COUNTMANYSHEETS("Sheet1,Sheet2,Sheet3", "G3:G1151", "COMPLETE")

    Notes:

    • It's important that you provide the sheet names separated by the separator specified in sheetNames = sheetNames.split(',');, and nothing else (not empty spaces after the comma, etc.).
    • It's important that you provide the range in quotes ("G3:G1151"). Otherwise, the function will interpret this as an array of values corresponding to the specified range, and you won't be able to look for the values in other sheets.
    • In this sample, the code looks for the string COMPLETE, and is case-sensitive. To make it case-insensitive, you could use toUpperCase() or toLowerCase().
    • If you wanted to look for all sheets in the spreadsheet, you could modify your function so that it only accepts the range and the text as parameters, and get all sheets via SpreadsheetApp.getActive().getSheets();.

    Reference: