COUNTIF Statements: Range Across All Sheets + Cell Reference as Criterion

Question

1) Range Across All Sheets:

I've googled everything but nothing. Basically, I need a formula that looks for the same range across all sheets.

My current formula looks like this:

=COUNTIF(Aug_15!$G:$G, "Shaun")+countif(July_15!$G:$G, "Shaun)+countif(June_15!$G:$G, "Shaun")+countif(May_15!$G:$G, "Shaun")+COUNTIF(Apr_15!$G:$G, "Shaun")+COUNTIF(Mar_15!$G:$G, "Shaun")

The issue I have is, as a month passes, a new sheet for the month is created. So this lowers the automation dramatically as you have to edit the formula every month. I'm basically looking for something that will search G:G across all sheets for that criteria.

So in my imaginary world, it would look something like this:

=COUNTIF(ALLSHEETS!$G:$G, "Shaun")

2) Cell Reference as Criterion

I'm trying to make the criteria look for something from another cell. For example, I'd replace "Shaun" with the cell L3. But it doesn't work! It searches for literally the two characters L and 3!

Is there anyway to make the criteria a value from another cell?

Many Thanks,

Shaun.

Answer 1

As Akshin Jalilov noticed, you will need a script to achieve that. I happen to have written a custom function for that scenario some time ago.

/**
 * Counts the cells within the range on multiple sheets.
 *
 * @param {"A1:B23"} range The range to monitor (A1Notation).
 * @param {"valueToCount"} countItem Either a string or a cell reference
 * @param {"Sheet1, Sheet2"} excluded [Optional] - String that holds the names of the sheets that are excluded (comma-separated list);
 * @return {number} The number of times the item appears in the range(s).
 * @customfunction
 */

function COUNTALLSHEETS(range, countItem, excluded) {
    try {
        var count = 0,
            ex = (excluded) ? Trim(excluded.split()) : false;
        SpreadsheetApp.getActive()
            .getSheets()
            .forEach(function (s) {
                if (ex && ex.indexOf(s.getName()) === -1 || !ex) {
                    s.getRange(range)
                        .getValues()
                        .reduce(function (a, b) {
                            return a.concat(b);
                        })
                        .forEach(function (v) {
                            if (v === countItem) count += 1;
                        });
                };
            });
        return count;
    } catch (e) {
        throw e.message;
    }
}

function Trim(v) {
    return v.toString().replace(/^\s\s*/, "")
    .replace(/\s\s*$/, "");
}

You can use the custom function in your spreadsheet like this:

=COUNTALLSHEETS("B2:B10", "Shaun")

or when 'Shaun' is in C2

=COUNTALLSHEETS("B2:B3", C2)

There is an optional parameter allowing you to provide a string with comma-separated sheet names you wish to exclude from the count. Don't use this paramater if you want to count ALL sheets.

See if that works for you ?