Excel - Using COUNTIF/COUNTIFS across multiple sheets/same column

Question

I am trying to "COUNT" the number of a certain object in column I (in this instance) across multiple sheets. That value in column I is the result of a formula (if it matters). So far I have:

=COUNTIF('Page M904'!I:I,A13)+COUNTIF('Page M905'!I:I,A13)+COUNTIF('Page M906'!I:I,A13)

which works, but I am going to have 20 something pages to scan through. I would like to avoid having a page long formula.

I have tried

=COUNTIFS('Page M904:Page M906'!I:I,A13) and =COUNTIF('Page M904:Page M906'!I:I,A13)

but that results in a #VALUE.

And I think

=COUNTIFS('Page M904'!I:I,A14,'Page M905'!I:I,A14,'Page M906'!I:I,A14)

is a misapplication of the COUNTIFS because I get 0 when it should be 35.

I am trying to avoid using VBA for this application. But if has to be, then it has to be :) Thanks in advance for your time and help.

Answer 1

This could be solved without VBA by the following technique.

In this example I am counting all the threes (3) in the range A:A of the sheets Page M904, Page M905 and Page M906.

List all the sheet names in a single continuous range like in the following example. Here listed in the range D3:D5.

Then by having the lookup value in cell B2, the result can be found in cell B4 by using the following formula:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&D3:D5&"'!A:A"), B2))

Answer 2

I am trying to avoid using VBA. But if has to be, then it has to be:)

There is quite simple UDF for you:

Function myCountIf(rng As Range, criteria) As Long
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        myCountIf = myCountIf + WorksheetFunction.CountIf(ws.Range(rng.Address), criteria)
    Next ws
End Function

and call it like this: =myCountIf(I:I,A13)

---

P.S. if you'd like to exclude some sheets, you can add If statement:

Function myCountIf(rng As Range, criteria) As Long
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        If ws.name <> "Sheet1" And ws.name <> "Sheet2" Then
            myCountIf = myCountIf + WorksheetFunction.CountIf(ws.Range(rng.Address), criteria)
        End If
    Next ws
End Function

UPD:

I have four "reference" sheets that I need to exclude from being scanned/searched. They are currently the last four in the workbook

Function myCountIf(rng As Range, criteria) As Long
    Dim i As Integer

    For i = 1 To ThisWorkbook.Worksheets.Count - 4
        myCountIf = myCountIf + WorksheetFunction.CountIf(ThisWorkbook.Worksheets(i).Range(rng.Address), criteria)
    Next i
End Function