Excel 函数教程：COUNTIFS 函数

概括

Excel 的 COUNTIFS 函数返回指定区域中满足一个或多个条件的单元格数量。每个条件都由一个单独的区域和条件指定，并且只有所有条件都为真，单元格才会被计入计数。COUNTIF 函数可用于统计包含日期、数字和文本的单元格数量。条件可以包含逻辑运算符（>、<、<>、=）和通配符（*、?）。

目的

统计符合多个条件的单元格数量

返回值

满足标准的次数

句法

=COUNTIFS(range1,criteria1,[range2],[criteria2],...)

range1 - 要评估的第一个范围。
criteria1 - 用于 range1 的标准。
range2 - [可选] 要评估的第二个范围。
criteria2 - [可选] 用于 range2 的条件。

使用 COUNTIFS 函数

COUNTIFS 函数用于统计指定区域中满足一个或多个特定条件的单元格数量。COUNTIFS 是 Excel 中最常用的函数之一，几乎所有基于日期、文本或数字执行条件计数的电子表格都会用到它。尽管 COUNTIFS 函数很常见，但它独特的逻辑条件被分为两部分。因此，COUNTIFS 函数的条件语法可能略微复杂，需要一些时间才能掌握。请记住，COUNTIFS 函数使用“与逻辑”——只有所有条件都为真，才能将单元格包含在最终计数中。

主要特点

统计满足一个或多个条件的值
每出现一种新情况，就需要制定新的范围和标准。
可处理日期、文本和数字
支持比较运算符（>、<、<>、=）和通配符（*、?）
要被纳入最终结果，所有条件必须都为真。
如果范围大小不一致，将返回 #VALUE! 错误。
自 Excel 2007 起，所有 Excel 版本均支持此功能。

与其他大多数 Excel 函数不同，COUNTIFS 函数要求条件区域必须为实际的单元格区域。如果您尝试使用数组，Excel 将不允许您输入公式。详情请参见以下示例。

句法

COUNTIFS 函数用于统计指定范围内满足一个或多个条件的单元格数量。其语法取决于要评估的条件数量。每个条件都以一对范围/条件参数的形式提供：

=COUNTIFS(range,criteria) // 1 condition
=COUNTIFS(range,criteria,range,criteria) // 2 conditions

如果有两个条件，就会有两个范围/条件对；如果有三个条件，就会有三个范围/条件对，依此类推。

所有范围的大小必须相同。如果提供不同的大小，COUNTIFS 将返回 #VALUE！

基本示例

下面的工作表仅包含订单数据，包括日期、颜色、州/省、数量和总计。使用 COUNTIFS 函数，我们可以轻松地统计符合以下条件的订单数量：

统计颜色为红色的订单数量。
统计颜色为红色且州为 TX 的订单数量。
统计颜色为红色且总数超过 20 的订单数量。
统计颜色为红色、总数超过 20 且州为 TX 的订单数量。

I5:I8 中的四个公式如下所示：

=COUNTIFS(C5:C16,"red")
=COUNTIFS(C5:C16,"red",D5:D16,"TX")
=COUNTIFS(C5:C16,"red",F5:F16,">20")
=COUNTIFS(C5:C16,"red",F5:F16,">20",D5:D16,"TX")

COUNTIFS 函数多条件示例

单元格 I8 中的公式含义是：计算颜色为“红色”且数量大于 20 且州为“TX”的行数。 结果为 2，因为有两行满足颜色为红色、数量大于 20 且州为 TX 的条件。每个条件都需要一个单独的区域/条件对，总共有三对。总而言之，以上公式中需要注意以下几点：

每个条件都需要单独的范围/条件对。
所有范围参数的大小必须相同。
条件出现的顺序并不重要。
条件可根据需要包含逻辑运算符（>、<、<>、<=、>=）。
对于“等于”条件，不需要等号（=）（即使用“red”而不是“=red”）。
COUNTIFS 函数不区分大小写，因此“red”和“Red”将返回相同的结果。
数字与运算符一起使用时必须用引号（“”）括起来（例如“">20”）。
COUNTIFS 使用“与”逻辑，所有条件都必须为真。

在 Excel 中，COUNTIF 函数的条件参数语法有些特殊。条件不能直接输入 >20，而必须用双引号括起来输入 >20”。如果不按要求用引号括起来，Excel 将不允许您输入公式。以下是更多语法示例。

应用标准

COUNTIFS 函数支持逻辑运算符（>、<、<>、=）和通配符（*、?），用于部分匹配。由于 COUNTIFS 函数属于将逻辑条件拆分为两部分的八个函数组，因此其语法略显复杂。每个条件都需要单独的范围和标准，并且运算符需要用双引号 ("") 括起来。下表列出了一些常见示例：

目标	标准
大于 75 的细胞	75”
单元格等于 100	100 或“100”
单元格小于或等于 100	"<=100"
等于“红色”的单元格	“红色的”
不等于“红色”的单元格	"<>红色"
空白单元格	""
非空白单元格	"<>"
以“X”开头的单元格	"x*"
细胞小于 A1	"<"&A1
细胞数量比今天少	"<"&TODAY()

请注意，最后两个示例使用了与号 (&)连接。当条件参数包含来自其他单元格的值或公式结果时，逻辑运算符（例如“<”）必须使用连接符连接。这是因为 Excel 需要先计算单元格引用和公式的值，然后才能将该值与运算符连接起来。

另一个单元格中的标准

通常，将条件放在另一个单元格中，然后在公式中引用该单元格会很方便。这样，以后无需编辑原始公式即可轻松更改条件。例如，您可以像这样计算 A1 单元格区域中与 A1 值相等的单元格数量：

=COUNTIFS(range,A1)

当条件需要运算符时，必须将单元格引用与运算符连接起来。例如，要计算大于 A1 的区域中的单元格数量，可以使用如下公式：

=COUNTIFS(range,">"&A1)

请注意，我们使用 & 符号将大于号 (">) 连接到单元格 A1。在下面的工作表中，COUNTIFS 函数已配置为返回大于 G4 单元格值的所有销售额的计数。请注意，大于号 (>) 是文本，必须用引号括起来。G5 单元格中的公式为：

=COUNTIFS(D5:D9,">"&G4) // count if greater than G4

COUNTIFS 函数示例 - 条件在另一个单元格中

不要将单元格引用用双引号括起来，例如“A1”。这样做会将其转换为文本。

不等于

要表达“不等于”条件，请使用双引号 ("") 括起来的“<>”运算符。例如，使用“<>red”表示“非红色”，使用“<>blue”表示“非蓝色”，如下表所示：

COUNTIFS 函数示例 - 统计不等于某个值的单元格数量

=COUNTIFS(B5:B9,"<>red") // not red
=COUNTIFS(B5:B9,"<>blue") // not blue
=COUNTIFS(B5:B9,"<>"&E7) // not E7

请注意以下事项：

在最后一个公式中，我们直接使用了 E7，所以我们需要像这样连接“<>”&E7。
不要在单元格引用周围加引号。
COUNTIFS 函数不区分大小写，因此“red”、“RED”和“Red”将返回相同的结果。

空白单元格

COUNTIFS 函数可以统计空白单元格和非空白单元格的数量。使用空字符串 ("") 可以指定空白单元格，使用“不等于”运算符 ("<>") 可以指定非空白单元格。在下面的工作表中，COUNTIFS 函数被配置为统计 D 列中的空白单元格和非空白单元格的数量：

COUNTIFS 函数示例 - 统计空白单元格和非空白单元格的数量

=COUNTIFS(D5:D9,"") // blank
=COUNTIFS(D5:D9,"<>") // not blank

第一种格式使用空字符串（“”）作为条件，这将统计空单元格。第二种格式使用“不等于”运算符（“<>”），这将统计非空单元格。请注意，第二种格式会统计包含任何值的单元格。如果需要更精确的统计，可以按如下方式调整条件：

=COUNTIFS(D5:D9,"x")

此公式只会统计包含“x”的单元格。

标准中的双引号 ("")

通常情况下，文本值需要用双引号括起来，而数字则不需要。但是，当数字中包含逻辑运算符时，数字和运算符都必须用引号括起来，如下所示：

=COUNTIFS(range,100) // count equal to 100
=COUNTIFS(range,">50") // count greater than 50
=COUNTIFS(range,"jim") // count equal to "jim"

注意：附加条件必须遵循相同的规则。

日期

在 Excel 中，日期是序列号，因此您可以像处理其他数字一样，对日期使用 <、>、<=、>= 等运算符。在 COUNTIFS 函数中使用日期时，难点在于如何以 Excel 能够识别的方式输入日期。使用 COUNTIFS 函数处理日期的最简单方法是引用另一个单元格中的有效日期。例如，如果 A1 单元格中有一个日期，您可以像这样统计大于 A1 的日期：

=COUNTIFS(range,">"&A1) // count dates greater than A1

如果要将日期硬编码到 COUNTIFS 函数中，最好的方法是使用DATE 函数。例如，要统计大于 2024 年 1 月 1 日的日期，可以这样使用 COUNTIFS：

=COUNTIFS(range,">"&DATE(2024,1,1) // count dates greater than 1-Jan-2024

下面的工作表使用了这两种方法：

COUNTIFS 函数示例 - 处理日期

F5:F8单元格中的公式如下：

=COUNTIFS(B5:B12,"<"&DATE(2024,3,1))
=COUNTIFS(B5:B12,">="&DATE(2024,3,31))
=COUNTIFS(B5:B12,">"&E10)
=COUNTIFS(B5:B12,">="&DATE(2024,5,1),B5:B12,"<="&DATE(2024,5,31))

请注意以下事项：

使用单元格引用时，必须将引用与运算符连接起来，例如“">”&E10。
DATE 函数保证 Excel 能正确解析日期。
引用另一个单元格中的日期，可以轻松更改日期而无需编辑公式。

专业提示：避免将日期直接硬编码到公式中。而是将日期放在一个单元格中，然后在公式中引用该单元格。这样，工作表会更加实用，因为您可以轻松查看所使用的日期，并在需要时更改日期而无需编辑公式。

外卡

COUNTIFS 函数支持三个通配符，可用于条件中：

星号 (*) - 匹配零个或多个字符
问号（？）——匹配任意一个字符
波浪号 (~) - 用于匹配字面通配符的转义字符

例如，要统计包含文本“apple”的字符串数量，可以使用如下公式：

=COUNTIFS(range,"*apple*") // cells that contain "apple"

要统计包含任意 3 个文本字符的单元格数量，可以使用以下方法：

=COUNTIFS(range,"???") // cells that contain any 3 characters

请注意，通配符仅适用于文本值。上面的公式不会将123 或 812 等三位数计入。下面的工作表展示了如何将通配符与 COUNTIFS 函数结合使用。F5:F8 单元格中的公式应用了 E 列中描述的条件。

=COUNTIFS(B5:B11,"mi*") // begins with "mi"
=COUNTIFS(B5:B11,"*ota") // ends with "ota"
=COUNTIFS(B5:B11,"????") // contains 4 characters
=COUNTIFS(B5:B11,"*~?") // ends with "?"

COUNTIFS 函数示例 - 使用通配符

请注意 F8 中的最后一个公式使用“*~?”来匹配“Montana?”和“Michigan?”末尾的问号 (?)。波浪号 (~) 是一个转义字符，可以用来匹配字面意义上的通配符。要匹配问号 (?)，请使用“~?”；要匹配星号 (*)，请使用“~*”；要匹配波浪号 (~)，请使用“~~”。下表显示了更多通配符的使用示例：

图案	行为	将匹配
？	任何一个角色	"A", "B", "c", "z", 等
？？	任意两个字符	"AA", "AZ", "zz", 等
？？？	任意三个字符	“Jet”、“AAA”、“ccc”等。
*	任何字符	“苹果”、“APPLE”、“A100”等。
*th	以“th”结尾	“浴室”、“第四”等等。
c*	以“c”开头	“猫”、“出租车”、“辛迪”、“糖果”等等。
？*	至少一个角色	“a”、“b”、“ab”、“ABCD”等。
???-??	五个字符，中间用连字符连接	“ABC-99”、“100-ZT”等。
*~?	以问号结尾	“喂？”、“有人在家吗？”等等。
xyz	包含“xyz”	“代码为 XYZ”、“100-XYZ”、“XyZ90”等。

注意：通配符仅适用于文本，不适用于数字。

或逻辑

COUNTIFS 函数设计用于应用多个“与”逻辑条件，因此没有直接的方法可以对具有“或”逻辑的单元格进行计数。但是，一种变通方法是将条件作为数组常量提供，例如 {"red","blue"}，然后将 COUNTIFS 公式嵌套在SUM 函数中，如下所示：

=SUM(COUNTIFS(range,{"red","blue"})) // count red or blue

上面的公式会统计区域中包含“红色”或“蓝色”的单元格数量。本质上，COUNTIFS 函数返回两个总和，一个用于计算“红色”单元格的数量，另一个用于计算“蓝色”单元格的数量，而 SUM 函数返回这两个总和。

汇总表

您可以使用 COUNTIFS 函数创建简单的汇总表。在下面的工作表中，F5:F9 单元格区域包含一个唯一颜色列表。目标是按颜色对 D 列中的金额进行小计。G5 单元格中的公式向下复制如下：

=COUNTIFS($C$5:$C$16,F5)

COUNTIFS 函数示例 - 创建一个简单的汇总表

请注意，该范围已锁定为绝对引用，以防止在向下复制公式时发生更改。如果您使用的是 Excel 2021 或更高版本，则可以使用如下所示的动态数组公式一次性生成所有总计：

=COUNTIFS(C5:C16,F5:F9)

在这种情况下，我们不需要绝对引用，因为单个公式即可生成所有结果。您还可以更进一步，在单元格 F5 中使用UNIQUE 函数获取唯一颜色列表，然后直接引用溢出区域，如下所示：

=COUNTIFS(C5:C16,F5#)

在 Excel 365 中，您还可以使用新的GROUPBY 函数创建像这样的汇总表。

数组问题

注意：对于更高级的用例，这是 COUNTIFS 功能的一个重要限制。

COUNTIFS 函数的一个比较棘手的限制是它不允许将数组作为范围参数。为了理解这个问题，请看下面的工作表，其中 B 列有 12 个日期，C 列有 12 个金额。我们的目标是创建一个公式，按年份统计金额。我们可以使用以下 COUNTIFS 公式来实现：

=COUNTIFS(B5:B16,">="&DATE(E5,1,1),B5:B16,"<="&DATE(E5,12,31))

注意：我通常会对这两个范围使用绝对引用，以便公式可以向下复制而不发生更改，但在这里我保留了相对地址，以便更容易阅读。

COUNTIFS 函数示例 - 数组问题练习题

这个公式虽然有效，但有点复杂。如果您对 Excel 公式有一定的经验，您可能会想到可以像这样巧妙地将 COUNTIFS 和 YEAR 函数结合使用：

=COUNTIFS(YEAR(B5:B16),E5)

我的想法是使用YEAR 函数从 B 列的日期中提取年份，然后使用 2024（单元格 E5）作为条件。如果这个方法有效就太好了。然而，Excel 甚至不允许你输入这个公式。如果你尝试输入，会收到一个通用的“公式有问题”错误提示。

COUNTIFS 函数示例 - 数组问题公式无法输入

问题在于 COUNTIFS 函数的 range 参数 需要一个正确的范围，但 YEAR(B5:B16) 会返回一个类似这样的数组：

{2024;2024;2024;2024;2024;2025;2025;2025;2025;2025;2025;2025}

需要说明的是，像这样使用 YEAR 在大多数其他公式中都能正常工作。但是，COUNTIFS 函数本身并不处理数组，因此这种方法行不通。我们该如何解决这个问题呢？一个不错的替代方案是改用 SUMPRODUCT 函数，并使用如下公式：

=SUMPRODUCT(--(range=criteria))

如果我们修改上面的模式以适应工作簿示例，我们会得到以下结果：

=SUMPRODUCT(--(YEAR(B5:B16)=E5))

这个公式比上面的 COUNTIFS 公式要简单得多。它很好地展示了 SUMPRODUCT 函数如何巧妙而优雅地解决棘手的问题。

请注意：如果您尝试为某个区域提供数组，则无法输入公式，因为 Excel 会显示“公式有问题”的错误对话框。“数组问题”并未明确提及。

局限性

COUNTIFS 函数有一些您应该了解的局限性：

COUNTIFS 函数不区分大小写。如需区分大小写，请使用EXACT 函数。
COUNTIFS 函数中的条件通过 AND 逻辑连接。所有条件必须都为 TRUE。对于简单的 OR 逻辑，请参见上文的方法。
COUNTIFS 函数的 range 参数必须指定一个实际的区间，不能使用数组。这意味着在应用条件之前，您无法在公式内部修改区间内的值。
COUNTIFS 函数无法正确计算超过 15 位数的长数字。例如：
如果您引用外部工作簿 中的某个区域，COUNTIFS 函数要求该工作簿必须处于打开状态才能进行计算。如果外部工作簿未打开，您将看到 #VALUE! 错误。作为一种解决方法，您可以改用 SUMPRODUCT 函数，该函数没有此限制。语法如下：=SUMPRODUCT(--(criteria_range=criteria))。

解决这些限制最常见的方法是使用SUMPRODUCT 函数。但是，最新版本的 Excel 提供了强大的替代方案，包括BYROW、BYCOL和GROUPBY 函数。