如何在多列范围中结合多条件高效使用COUNTIFS函数？

示例数据 #

现有可行统计方式 #

• 统计A、B、C列中所有foo的数量：

  =COUNTIFS($A:$C,"=foo")
  

• 统计A列中满足Type=1且Value=2的foo数量：

  =COUNTIFS($A:$A,"=foo", $D:$D,"=1", $E:$E,"=2")
  

问题痛点 #

直接用COUNTIFS同时统计A/B/C列中符合Type=1且Value=2的foo数量会报错（LibreOffice Calc返回Err:502）：

=COUNTIFS($A:$C,"=foo", $D:$D,"=1", $E:$E,"=2")   # Err:502 (LibreOffice Calc中)

拆分多列分别统计再相加的方法可行，但列数较多时操作繁琐、难以维护：

=COUNTIFS($A:$A,"=foo", $D:$D,"=1", $E:$E,"=2") +
 COUNTIFS($B:$B,"=foo", $D:$D,"=1", $E:$E,"=2") +
 COUNTIFS($C:$C,"=foo", $D:$D,"=1", $E:$E,"=2")

高效解决方案 #

方案1：SUMPRODUCT函数（全版本兼容） #

利用数组运算一次性完成判断与求和，兼容LibreOffice Calc和所有Excel版本：

=SUMPRODUCT(($A$2:$A$9="foo")+($B$2:$B$9="foo")+($C$2:$C$9="foo"), ($D$2:$D$9=1)*($E$2:$E$9=2))

• ($A$2:$A$9="foo")+($B$2:$B$9="foo")+($C$2:$C$9="foo")：逐行计算当前行A/B/C列中foo的个数，生成对应数组
• ($D$2:$D$9=1)*($E$2:$E$9=2)：逐行判断是否满足Type=1且Value=2，满足返回1，否则返回0，生成条件数组
• SUMPRODUCT将两个数组对应元素相乘后求和，最终得到符合要求的foo总数量

方案2：BYROW+SUM组合（支持动态数组版本） #

如果使用Excel 365或LibreOffice 7.4及以上版本，可借助动态数组函数实现更直观的写法：

=SUM(BYROW($A$2:$C$9, LAMBDA(row, SUM(--(row="foo")))) * ($D$2:$D$9=1) * ($E$2:$E$9=2))

• BYROW($A$2:$C$9, LAMBDA(row, SUM(--(row="foo"))))：遍历A/B/C列的每一行，计算该行foo的个数，生成结果数组
• 再与D、E列的条件判断数组相乘，最后用SUM求和得到总数

方案3：辅助列法（易维护） #

适合需要频繁调整列或条件的场景，逻辑清晰易懂：

1. 在F2单元格输入公式=COUNTIF($A2:$C2,"foo")，下拉填充至所有数据行，该列将记录每一行A/B/C列中foo的数量
2. 使用SUMIFS统计符合条件的辅助列总和：

   =SUMIFS($F$2:$F$9, $D$2:$D$9,1, $E$2:$E$9,2)
   

内容的提问来源于stack exchange，提问作者lonix