在Excel中使用OR逻辑进行多条件计数
通常,您可以使用COUNTIFS函数根据一个或多个条件(使用AND逻辑)在Excel中统计单元格数量。您是否遇到过需要从单列或多行单元格中统计多个值的情况?这意味着要使用多条件和OR逻辑进行统计。在这种情况下,您可以结合使用SUM和COUNTIFS函数,或者使用SUMPRODUCT函数来实现。
在Excel中按OR条件统计单元格
例如,我有如下截图所示的数据范围,现在我想统计产品为“铅笔”或“尺子”的数量。在这里,我将讨论两种公式来解决这个任务。
使用SUM和COUNTIFS函数按OR条件统计单元格
在Excel中,要按多个OR条件进行统计,可以使用带有数组常量的SUM和COUNTIFS函数,通用语法为:
- 范围: 包含条件的数据范围,您将从中统计单元格;
- 条件1, 条件2, 条件3…: 您希望基于的统计单元格的条件。
要统计产品为“铅笔”或“尺子”的数量,请将以下公式复制或输入到空白单元格中,然后按Enter键获取结果:
公式解释:
=SUM(COUNTIFS(B2:B13,{"铅笔","尺子"}))
- {"铅笔","尺子"}: 首先,您应将所有条件打包在一个数组常量中,如:{"铅笔","尺子"},并用逗号分隔各项。
- COUNTIFS(B2:B13,{"铅笔","尺子"}): 此COUNTIFS函数将分别统计“铅笔”和“尺子”的数量,并得到这样的结果:{2,3}。
- SUM(COUNTIFS(B2:B13,{"铅笔","尺子"}))=SUM({2,3}): 最后,此SUM函数将数组中的所有项相加并返回结果:5。
提示:您也可以使用单元格引用作为条件,请应用以下数组公式,然后按Ctrl + Shift + Enter键一起获取正确结果:
使用SUMPRODUCT函数按OR条件统计单元格
这里还有另一个由SUMPRODUCT函数创建的公式,可以帮助按OR逻辑统计单元格。通用语法为:
- 范围: 包含条件的数据范围,您将从中统计单元格;
- 条件1, 条件2, 条件3…: 您希望基于的统计单元格的条件。
请将以下公式复制或输入到空白单元格中,并按Enter键返回结果:
公式解释:
=SUMPRODUCT(1*(B2:B13={"铅笔","尺子"}))
- B2:B13={"铅笔","尺子"}: 该表达式将每个条件“铅笔”、“尺子”与范围单元格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={"铅笔","尺子"}): 乘法将逻辑值 - 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={"铅笔","尺子"}))= 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函数,或者使用带有ISNUMBER MATCH函数的SUMPRODUCT函数。
使用SUM和COUNTIFS函数按两组OR条件统计单元格
要处理只有两组OR条件的情况,只需在COUNTIFS公式中添加另一个数组常量即可。
例如,我有如下截图所示的数据范围,现在我想统计订购了“铅笔”或“尺子”且金额小于100或大于200的人数。
请将以下公式输入或复制到单元格中,然后按Enter键获取结果,见截图:
注意:在公式中,您应该对第二个数组常量使用分号,这会创建一个垂直数组。
使用SUMPRODUCT函数按多组OR条件统计单元格
上述公式仅适用于两组OR条件,如果您需要按更多条件进行统计,复杂的SUMPRODUCT公式结合ISNUMBER MATCH函数可以帮助您。
以以下数据为例,统计订购了“铅笔”或“尺子”且状态为“已交付”或“运输中”并且由“Bob”或“Eko”签署的人数,您应该应用复杂公式。
请将以下公式复制或输入到空白单元格中,然后按Enter键获取计算结果,见截图:
公式解释:
=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"铅笔","尺子"},0))*ISNUMBER(MATCH(C2:C13,{"已交付","运输中"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0)))
ISNUMBER(MATCH(B2:B13,{"铅笔","尺子"},0)):
- MATCH(B2:B13,{"铅笔","尺子"},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,{"铅笔","尺子"},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,{"铅笔","尺子"},0))*ISNUMBER(MATCH(C2:C13,{"已交付","运输中"},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。
相关功能说明:
- SUM:
- Excel中的SUM函数返回提供的值的总和。
- COUNTIF:
- COUNTIF函数是Excel中的统计函数,用于统计满足特定条件的单元格数量。
- SUMPRODUCT:
- SUMPRODUCT函数可用于将两列或多列或数组相乘,然后获取乘积的总和。
- ISNUMBER:
- Excel中的ISNUMBER函数当单元格包含数字时返回TRUE,否则返回FALSE。
- MATCH:
- Microsoft Excel中的MATCH函数用于在单元格范围内搜索特定值,并返回该值的相对位置。
更多文章:
- 基于条件统计唯一数值的数量
- 在Excel工作表中,您可能会遇到一个问题,即基于特定条件统计唯一数值的数量。例如,如何从如下截图所示的报告中统计产品为“T恤”的唯一数量值?本文将展示一些在Excel中实现此任务的公式。
- 统计符合两个或多个条件的单元格
- 在Excel中,要基于两个或多个条件统计单元格数量,COUNTIFS函数可以帮助您快速轻松地解决此任务。
- 按多个OR条件统计行数
- 要在不同列中按多个条件统计行数,使用OR逻辑,SUMPRODUCT函数可以帮助您。例如,我有如下截图所示的产品报告,现在我想统计产品为“T恤”或颜色为“黑色”的行数。如何在Excel中处理此任务?
最佳的办公生产力工具
Kutools for Excel - 助您脱颖而出
🤖 | Kutools AI 助手:基于智能执行革新数据分析:智能执行 | 生成代码 | 创建自定义公式 | 分析数据并生成图表 | 调用 Kutools 函数… |
热门功能:查找、高亮或标记重复项 | 删除空行 | 合并列或单元格而不丢失数据 | 无需公式四舍五入 ... | |
超级 VLookup:多条件 | 多值 | 跨多表 | 模糊查找... | |
高级下拉列表:简易下拉列表 | 级联下拉列表 | 多选下拉列表... | |
列管理器: 添加指定数量的列 | 移动列 | 切换隐藏列的可见状态 | 比较列以 选择相同和不同单元格 ... | |
特色功能:网格聚焦 | 设计视图 | 增强编辑栏 | 工作簿与工作表管理器 | 资源库(自动文本) | 日期提取 | 合并工作表 | 加密/解密单元格 | 按列表发送电子邮件 | 超级筛选 | 特殊筛选(筛选粗体/斜体/删除线...) ... | |
排名前 15 的工具集:12 个文本工具(添加文本,删除特定字符 ...) | 50+ 种图表 类型(甘特图 ...) | 40+ 实用 公式(基于生日计算年龄 ...) | 19 个插入工具(插入二维码,从路径插入图片 ...) | 12 个转换工具(小写金额转大写,汇率转换 ...) | 7 个合并和拆分工具(高级合并行,拆分 Excel 单元格 ...) | ... 更多功能 |
Kutools for Excel 拥有超过 300 项功能,确保您所需的一切都仅需一键之遥...
Office Tab - 在 Microsoft Office(包括 Excel)中启用标签式阅读和编辑
- 一秒内在数十个打开的文档之间切换!
- 每天为您减少数百次鼠标点击,告别鼠标手。
- 在查看和编辑多个文档时,您的生产力可提升 50%。
- 为 Office(包括 Excel)带来高效标签页,就像 Chrome、Edge 和 Firefox 一样。