Excel - using COUNTIF / COUNTIFS for multiple sheets / single column

Question

I am trying to "COUNT" indicate the number of a specific object in column I (in this case) on multiple sheets. This value in column i is the result of the formula (if that matters). So far, I:

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

which works, but I will have 20 pages to scan. I would like to avoid using a long page formula.

I tried

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

but this leads to a #VALUE .

And I think,

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

is the wrong use of COUNTIFS because I get 0 when it should be 35.

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

Answer 1

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

There is a fairly 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 name it like this: =myCountIf(I:I,A13)

---

PS if you want to exclude some sheets, you can add an 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 “help” sheets that I need to exclude from check / search. They are currently the last in the book.

 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 

Answer 2

This can be solved without VBA using the following technique.

In this example, I calculate all three (3) in the range A:A sheets Page M904 , Page M905 and Page M906 .

List all sheet names in one continuous range, as shown in the following example. Listed in the range D3:D5 .

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

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

Answer 3

My first post ... I managed to compile UDF quickly. Application: Select the 3D range as usual and enclose it in quotation marks as shown below.

= CountIf3D ("'StartSheet: EndSheet' G16: G878"; "criteria")

Recommended sheets should be contiguous to avoid unexpected results.

 Public Function CountIf3D(SheetstoCount As String, CriteriaToUse As Variant) Dim sStarSheet As String, sEndSheet As String, sAddress As String Dim lColonPos As Long, lExclaPos As Long, cnt As Long lColonPos = InStr(SheetstoCount, ":") 'Finding ':' separating sheets lExclaPos = InStr(SheetstoCount, "!") 'Finding '!' separating address from the sheets sStarSheet = Mid(SheetstoCount, 2, lColonPos - 2) 'Getting first sheet name sEndSheet = Mid(SheetstoCount, lColonPos + 1, lExclaPos - lColonPos - 2) 'Getting last sheet name sAddress = Mid(SheetstoCount, lExclaPos + 1, Len(SheetstoCount) - lExclaPos) 'Getting address cnt = 0 For i = Sheets(sStarSheet).Index To Sheets(sEndSheet).Index cnt = cnt + Application.CountIf(Sheets(i).Range(sAddress), CriteriaToUse) Next CountIf3D = cnt End Function