Excel - 跨多个工作表/同一列使用COUNTIF / COUNTIFS [英] Excel - Using COUNTIF/COUNTIFS across multiple sheets/same column

问题描述

我正在尝试在多个工作表中列出第一列（在这种情况下）中的某个对象的数量。第一列中的值是公式的结果（如果重要）。到目前为止，我有：

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（'页M904'！I：I，A13）+ COUNTIF（'页M905'！I：我，A13）+ COUNTIF（'页M906'！我：我，A13）

哪些工作，但我将有20要浏览的页面。我想避免一个页面长的公式。

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

我尝试过

= COUNTIFS（'Page M904：Page M906'！I：I，A13）和
= COUNTIF（'Page M904：Page M906'！I： A13）

，但会导致 #VALUE 。

我认为

= COUNTIFS（'页面M904'！I：I，A14，'页面M905'！I：I，A14，'Page M906'！I：I，A14）

是错误的 COUNTIFS 因为我得到 0 ，当它应该是35。

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

我我试图避免使用VBA进行此应用程序。但是如果必须的话，那么它必须是:)谢谢你的时间和帮助。

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.

推荐答案

$ b $我试图避免使用VBA。

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

有一个非常简单的UDF，你可以：

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

并调用如下： = myCountIf（I：I，A13）

PS如果您想排除某些工作表，可以添加如果语句：

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

这篇关于Excel - 跨多个工作表/同一列使用COUNTIF / COUNTIFS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！