Excel 函数教程：COUNTIF 函数

概括

Excel 的 COUNTIF 函数用于统计指定区域中满足特定条件的单元格数量。其通用语法为 COUNTIF(区域, 条件)，其中“区域”包含要统计的单元格，“条件”是单元格必须满足的条件。COUNTIF 可用于统计包含日期、数字和文本的单元格。条件可以包含逻辑运算符（>、<、<>、=）和通配符（*、?）。

目的

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

返回值

代表细胞计数结果的数字。

句法

=COUNTIF(range,criteria)

范围- 要计数的单元格范围。
标准——控制哪些细胞应该被计数的标准。

使用 COUNTIF 函数

COUNTIF 函数用于统计指定范围内满足特定条件的单元格数量。COUNTIF 是 Excel 中最常用的函数之一，您会在各种基于日期、文本或数字进行条件计数的电子表格中找到它。COUNTIF 函数功能强大，但其独特的语法将逻辑条件拆分为两部分，这使其与其他许多 Excel 函数有所不同。这种语法需要一些时间来适应。请记住，COUNTIF 函数一次只能应用一个条件。如果需要多个条件，请使用COUNTIFS 函数。

主要特点

统计满足单个条件的单元格值（对于多个条件，请使用COUNTIFS 函数）
可处理日期、文本和数字
支持比较运算符（>、<、<>、=）和通配符（*、?）
适用于所有 Excel 版本

与其他大多数 Excel 函数不同，COUNTIF 函数的range参数必须指定一个实际的单元格区域。如果尝试使用数组，Excel 将不允许您输入公式。详情请参见以下示例。

基本示例

COUNTIF 函数的通用语法如下：

=COUNTIF(range,criteria)

该条件应用于指定范围内的单元格。当单元格符合条件时，它们将被添加到计数中。在下面的工作表中，我们有一些销售数据。我们使用 COUNTIF 函数在三个公式中执行以下计算：（1）统计所有超过 100 美元的销售额，（2）统计所有由 Jim 进行的销售额，以及（3）统计所有在加利福尼亚州进行的销售额。G5:G7 单元格区域中的公式如下所示：

=COUNTIF(D5:D16,">100") // count sales over 100
=COUNTIF(B5:B16,"jim") // count sales by Jim
=COUNTIF(C5:C16,"CA") // count sales in CA

COUNTIF 函数 - 基本示例

请注意以上公式的以下几点：

对于“等于”条件，不需要等号（=）（即，使用“jim”而不是“=jim”）。
COUNTIF 函数不区分大小写，因此“jim”、“Jim”和“JIM”将返回相同的结果。
数字与运算符一起使用时必须用引号（“”）括起来（例如“>100”）。

请注意，Excel 中 COUNTIF 函数的条件参数语法略有不同。条件不能直接输入 >100，而必须用双引号括起来输入 >100”。如果不按要求用双引号括起来，Excel 将不允许您输入公式。以下提供了更多语法示例。

应用标准

COUNTIF 函数支持逻辑运算符（>、<、<>、=）和通配符（*、?），用于部分匹配。使用 COUNTIF 函数的难点在于应用条件所需的语法。这是因为 COUNTIF 函数属于一组八个函数，这些函数将逻辑条件分为两部分：范围和条件。因此，运算符必须用双引号 ("") 括起来。下表列出了常用条件所需的语法示例：

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

请注意最后两个示例都使用了& 符号进行连接。任何时候，当您将另一个单元格中的值（或公式的结果）与逻辑运算符（例如“<”）一起使用时，都必须使用连接符。这是因为 Excel 需要先计算单元格引用和公式，然后才能将该值与运算符一起使用。

另一个单元格中的标准

使用 COUNTIF 函数的一个好方法是将条件放在另一个单元格中，然后在公式中引用该单元格。这样，以后无需修改原始公式即可轻松更改条件。例如，您可以像这样计算 A1 单元格区域中与 A1 值相等的单元格数量：

=COUNTIF(range,A1)

如果需要在条件中包含运算符，则必须将单元格引用与运算符连接起来。例如，要统计大于 A1 的单元格数量，可以使用如下公式：

=COUNTIF(range,">"&A1)

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

=COUNTIF(D5:D16,">"&F5) // count if greater than G4

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

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

不等于

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

COUNTIF 函数示例 - 不等于

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

请注意以下事项：

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

空白单元格

COUNTIF 函数可以统计空白单元格和非空白单元格的数量。使用空字符串 ("") 可以指定空白单元格，使用“不等于”运算符 ("<>") 可以指定非空白单元格。在下面的工作表中，COUNTIF 函数用于统计空白单元格和非空白单元格的数量，具体取决于 D 列是否包含“x”或为空：

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

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

结果是，有 2 张“未付款”（空白）发票和 3 张已付款发票。更准确地说，您可以使用如下公式，该公式仅统计 D 列包含“x”的单元格：

=COUNTIF(D5:D9,"x") // equals "x"

日期

在 Excel 中，日期是一长串数字，因此您可以像处理其他数字一样，对日期使用 <、>、<= 和 >= 等运算符。在 COUNTIF 函数中使用日期作为条件时，难点在于如何以 Excel 能够识别的方式输入日期。最可靠的方法是引用另一个单元格中的日期，或者使用DATE 函数。下面的工作表展示了这两种方法：

COUNTIF 函数示例 - 处理日期

=COUNTIF(B5:B9,"<"&DATE(2024,3,1))
=COUNTIF(B5:B9,">="&DATE(2024,4,1))
=COUNTIF(B5:B9,">"&E9)

请注意以下事项：

当使用带运算符的函数时，必须像这样连接：`"<"&DATE(2014,3,1)`
当使用单元格引用和运算符时，必须像这样连接起来：">"&E9。

COUNTIF 函数一次只能应用一个条件。要统计两个日期之间的日期数量，您需要改用COUNTIFS 函数，该函数可以根据多个条件对单元格进行计数。

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

外卡

COUNTIF 函数支持三个通配符，您可以在匹配条件中使用它们，以实现更灵活的匹配：

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

下面的工作表展示了如何将通配符与 COUNTIF 函数结合使用。F5:F8 单元格中的公式应用了 E 列中描述的条件。

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

COUNTIF 函数示例 - 使用通配符

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

图案	行为	将匹配
？	任何一个角色	"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”等。

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

或逻辑

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

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

上面的公式将统计指定范围内包含“红色”或“蓝色”的单元格数量。本质上，COUNTIF 函数返回一个数组，其中包含两个计数（一个代表“红色”，一个代表“蓝色”），而 SUM 函数返回这两个计数的总和。以下示例将更详细地解释这一概念。

汇总表

您可以使用 COUNTIF 函数创建一个简单的计数汇总表。在下面的工作表中，F5:F9 单元格区域包含一个唯一颜色列表。目标是统计每种颜色的数量。G5 单元格中的公式向下复制如下：

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

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

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

=COUNTIF(C5:C16,F5:F9)

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

=COUNTIF(C5:C16,F5#)

使用溢出范围的优点是，如果唯一颜色列表发生变化，计数也会随之更新。

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

数组问题

COUNTIF 函数的一个棘手限制是它不允许将数组作为范围参数。这意味着你不能像传递范围参数一样，将来自其他函数的数组传递给 COUNTIF 函数。为了理解这个问题，请看下面的工作表，其中 B 列有 12 个日期，C 列有 12 个金额。目标是创建一个公式来统计每年的条目数。如果你有一些 Excel 公式的使用经验，你可能会认为可以像这样巧妙地将 COUNTIF 和 YEAR 函数结合使用：

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

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

COUNTIF 函数示例——“数组问题”

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

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

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

=SUMPRODUCT(--(YEAR(range)=value))

双重否定（--）可以将 TRUE 和 FALSE 值转换为 1 和 0。如果我们把上面的模式应用到工作簿示例中，就会得到以下结果：

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

COUNTIF 函数示例 - 数组问题的解决方法

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

另一种方法是改用 COUNTIFS 函数，并使用两个条件来获取给定年份中的所有日期，公式如下：

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

然而，这个公式比较复杂，我更喜欢使用 SUMPRODUCT 函数。事实上，这正是 SUMPRODUCT 函数如何巧妙而优雅地解决棘手问题的绝佳例子。

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

局限性

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

COUNTIF 函数仅支持单个条件。要统计满足多个条件的单元格数量，请使用COUNTIFS 函数。
COUNTIF 函数不区分大小写。如需区分大小写，请使用EXACT 函数。
COUNTIF 函数的 range 参数需要一个实际的单元格区域，不能使用数组。这意味着在应用条件之前，您无法在公式中修改单元格区域内的值。
COUNTIF 函数无法正确计算超过 15 位数的长数字。例如：
如果您引用外部工作簿 中的某个范围，COUNTIF 函数要求该工作簿必须处于打开状态才能计算结果。如果外部工作簿未打开，您将看到 #VALUE! 错误。作为一种解决方法，您可以改用 SUMPRODUCT 函数，该函数没有此限制。语法如下：=SUMPRODUCT(--(criteria_range=criteria))。
本文将解释 COUNTIF 函数的其他一些特性。

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