Excel 中基于多条件 OR 逻辑的 Countifs 用法

通常，您可以使用 COUNTIFS 函数，根据一个或多个条件（AND 逻辑）统计 Excel 中的单元格数量。但有时，您可能需要统计某一列或区域中包含多个值的单元格数量，也就是基于多个条件的 OR 逻辑进行统计。此时，您既可以结合 SUM 和 COUNTIFS 函数使用，也可以选择 SUMPRODUCT 函数来实现。

• 在 Excel 中根据 OR 条件统计单元格数量
  • 使用 SUM 和 COUNTIFS 函数，根据 OR 条件统计单元格数量
  • 使用 SUMPRODUCT 函数，根据 OR 条件统计单元格数量
• 在 Excel 中统计多组 OR 条件对应的单元格数量
  • 使用 SUM 和 COUNTIFS 函数，根据两组 OR 条件统计单元格数量
  • 使用 SUMPRODUCT 函数，根据多组 OR 条件统计单元格数量

在 Excel 中根据 OR 条件统计单元格数量

例如，下图展示了一组数据。如果您想统计产品为“Pencil”或“Ruler”的数量，下面将为您介绍两种 Excel 公式来解决这一问题。

使用 SUM 和 COUNTIFS 函数，根据 OR 条件统计单元格数量

在 Excel 中，若要根据多个 OR 条件计数，可以搭配数组常量使用 SUM 和 COUNTIFS 函数，通用语法如下：

• range：包含要统计单元格条件的数据区域；
• criterion 1, criterion 2, criterion 3…：您希望用于统计单元格的条件。

要统计产品为“Pencil”或“Ruler”的数量，请将以下公式复制或输入到空白单元格中，然后按 Enter 键即可得到结果：

公式说明：

=SUM(COUNTIFS(B2:B13,{"Pencil","Ruler"}))

• {"Pencil","Ruler"}：首先，您需要将所有条件打包为类似 {“Pencil“，“Ruler“} 的数组常量，数组项之间用逗号分隔。
• COUNTIFS(B2:B13,{"Pencil","Ruler"})：该 COUNTIFS 函数会分别统计“Pencil”和“Ruler”的数量，结果为：{2,3}。
• SUM(COUNTIFS(B2:B13,{"Pencil","Ruler"}))=SUM({2,3})：最后，SUM 函数会对数组中的所有项进行求和，返回结果为 5.

提示：也可将单元格引用用作条件，使用下方数组公式，并同时按 Ctrl + Shift + Enter 键组合以获得正确结果：

使用 SUMPRODUCT 函数，根据 OR 条件统计单元格数量

还有另一种快捷公式，即通过 SUMPRODUCT 函数统计具备 OR 逻辑的单元格，通用语法如下：

• range：包含要统计单元格条件的数据区域；
• criterion 1, criterion 2, criterion 3…：您希望根据其统计单元格的条件。

请将下列公式复制或输入到空白单元格，并按 Enter 键返回结果：

公式说明：

=SUMPRODUCT(1*(B2:B13={"Pencil","Ruler"}))

• B2:B13={"Pencil","Ruler"}：该表达式会将“Pencil”和“Ruler”分别与 B2:B13 区域内的每个单元格进行比较。若条件成立，则返回 TRUE，否则返回 FALSE。结果如下：{TRUE，FALSE;FALSE，FALSE;FALSE，FALSE;FALSE，TRUE;FALSE，FALSE;TRUE，FALSE;FALSE，FALSE;FALSE，TRUE;FALSE，FALSE;FALSE，FALSE;FALSE，TRUE;FALSE，FALSE}。
• 1*(B2:B13={"Pencil","Ruler"})：通过乘法运算，将逻辑值 TRUE 和 FALSE 分别转换为 1 和 0，最终得到如下结果：{1,0;0,0;0,0;0,1;0,0;1，0;0，0;0,1;0,0;0,0;0,1;0,0}。
• SUMPRODUCT(1*(B2:B13={"Pencil","Ruler"}))= SUMPRODUCT({1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0})：最后，SUMPRODUCT 函数会对数组中的所有数字求和，得出结果：5.

在 Excel 中统计多组 OR 条件对应的单元格数量

有时，您可能需要根据两组或多组 OR 条件统计单元格数量。这时，您可以选择将 SUM 与 COUNTIFS 结合数组常量，或使用 SUMPRODUCT 搭配 ISNUMBER 和 MATCH 函数来实现。

使用 SUM 和 COUNTIFS 函数，根据两组 OR 条件统计单元格数量

如仅需处理两组 OR 条件，只需在 COUNTIFS 公式中额外添加一个数组常量即可。

以数据区域为例，如下图所示，现在需要统计订购了“Pencil”或“Ruler”且金额为 200 的人数。

请将下列公式输入或复制到单元格中，然后按 Enter 键获取结果，见截图：

：在此公式中，第二个数组常量需使用分号，以生成纵向数组。

使用 SUMPRODUCT 函数，根据多组 OR 条件统计单元格数量

上述公式仅适用于两组 OR 条件。如果需要统计更多条件，可以结合 SUMPRODUCT、ISNUMBER 和 MATCH 函数，使用更为复杂的公式来实现。

以以下数据为例，若需统计订购了“Pencil”或“Ruler”，且状态为“Delivered”或“In transit”，并且签收人为“Bob”或“Eko”的人数，则需要使用更为复杂的公式。

请将下列公式复制或输入到空白单元格，并按 Enter 键运行并查看计算结果，见截图：

公式说明：

=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0)))

ISNUMBER(MATCH(B2:B13，{“Pencil“，“Ruler“}，0)）：

• MATCH(B2:B13,{"Pencil","Ruler"},0)：此 MATCH 函数可将 B2:B13 区域中的每个单元格与数组常量进行比较，若找到匹配项，则返回其在数组中的相对位置，否则返回错误值。最终，您将获得如下数组：{1；#N/A；#N/A;2；#N/A;1；#N/A;2;1；#N/A;2；#N/A}。
• ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))= ISNUMBER({1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;#N/A})：ISNUMBER 函数会将数字转换为 TRUE，将错误值转换为 FALSE，结果如下：{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE}。

上述逻辑同样适用于第二个和第三个 ISNUMBER 表达式。

SUMPRODUCT(ISNUMBER(MATCH(B2:B13，{“Pencil“，“Ruler“}，0)）*ISNUMBER(MATCH(C2:C13，{“Delivered“，“In transit“}，0)）*ISNUMBER(MATCH(D2:D13，{“Bob“，“Eko“}，0)））：

• 然后，这三个数组会在 SUMPRODUCT 函数中相乘，TRUE 和 FALSE 在运算时会自动转换为 1 和 0. 例如：SUMPRODUCT（{1;0;0;1;0;1;0;1;1;0;1;0}*{1;1;0;0;1;1;0;1;1;0;1;1}*{1;0;0;0;0;0;0;1;0;0;1;0}）=SUMPRODUCT（{1;0;0;0;0;0;0;1;0;0;1;0}）。
• 最后，SUMPRODUCT 函数会对数组中的所有数字进行求和，最终得出结果：3.

相关函数说明：

更多相关文章：