vba Excel - 在多个工作表/同一列中使用 COUNTIF/COUNTIFS

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

提问by R West

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:

我正在尝试跨多个工作表“计算”I 列（在本例中）中某个对象的数量。第 I 列中的那个值是公式的结果（如果重要的话）。到目前为止，我有：

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

=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.

这行得通，但我要扫描 20 页。我想避免使用一页长的公式。

I have tried

我试过了

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

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

but that results in a #VALUE.

但这会导致#VALUE.

And I think

我认为

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

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

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

是对 the 的误用，COUNTIFS因为我得到了0它应该是 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.

我试图避免在此应用程序中使用 VBA。但如果必须是，那么它必须是 :) 提前感谢您的时间和帮助。

采纳答案by Dmitry Pavliv

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

我试图避免使用 VBA。但如果必须是，那么它必须是:)

There is quite simple UDF for you:

有一个非常简单的 UDF 供您使用：

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)

并这样称呼它： =myCountIf(I:I,A13)

---

---

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

PS如果你想排除一些工作表，你可以添加If语句：

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

回答by Netloh

This could be solved without VBA by the following technique.

这可以通过以下技术在没有 VBA 的情况下解决。

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

在这个例子中我计数范围中的所有的三分（3）A:A片材的Page M904，Page M905和Page M906。

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

列出单个连续范围内的所有工作表名称，如下例所示。这里列出的范围D3:D5。

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

然后通过在单元格中查找值，B2可以B4使用以下公式在单元格中找到结果：

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

回答by Armianas

My first post... UDF I managed quickly to compile. Usage: Select 3D range as normal and enclose is into quotation marks like below...

我的第一篇文章... UDF 我设法快速编译。用法：正常选择 3D 范围并用引号括起来，如下所示...

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

=CountIf3D("'StartSheet:EndSheet'!G16:G878";"标准")

Advisably sheets to be adjacent to avoid unanticipated 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's name
    sEndSheet = Mid(SheetstoCount, lColonPos + 1, lExclaPos - lColonPos - 2) 'Getting last sheet's 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

回答by Matthew Olson

I was looking to do the same thing, and I have a work around that seems to be less complicated using the Frequency and Index functions. I use this part of the function from averaging over multiple sheets while excluding the all the 0's.

我想做同样的事情，我有一个解决方法，使用频率和索引函数似乎不那么复杂。我使用这部分函数对多张纸进行平均，同时排除所有的 0。

=(FREQUENCY(Start:End!B1,-0.000001)+INDEX(FREQUENCY(Start:End!B1,0),2))