Excel多组可变复合条件下的Value列求和公式实现

原始数据集 #

复合过滤条件（可变数量，示例3组，空值代表匹配任意） #

问题 #

试过用下面的公式求和，但这个写法只能处理单列条件，没法应对多组复合过滤规则：

SUMPRODUCT(SUMIFS(value;'Criteria A from data;Criteria A from filter;'Criteria B from data;Criteria B from filter;'Criteria C from data;Criteria C from filter))

需要算出满足任意一组复合条件的数值总和，预期结果是65。

解决方法 #

适用于Excel 365/2021的动态数组公式 #

用BYROW遍历每组过滤条件，结合SUMIFS处理空值匹配任意的逻辑，最后把各组结果加起来：

=SUM(BYROW(F2:H4, LAMBDA(r, SUMIFS(D:D, A:A, IF(INDEX(r,1)="", "*", INDEX(r,1)), B:B, IF(INDEX(r,2)="", "*", INDEX(r,2)), C:C, IF(INDEX(r,3)="", "*", INDEX(r,3))))))

• 把F2:H4换成你的过滤条件所在区域
• D:D是数值列，A:A/B:B/C:C对应条件A/B/C列

兼容旧版Excel的数组公式 #

如果用的是旧版Excel，输入下面的公式后按Ctrl+Shift+Enter确认（必须以数组公式执行）：

=SUMPRODUCT(SUMIFS(D:D,A:A,IF(F2:F4="","*",F2:F4),B:B,IF(G2:G4="","*",G2:G4),C:C,IF(H2:H4="","*",H2:H4)))

结果说明 #

按示例条件计算：

• 第一组条件匹配到数值14
• 第二组条件无匹配数据，贡献0
• 第三组条件匹配到数值11+14=25
  （注：若预期结果为65，需检查过滤条件是否存在笔误，公式逻辑可适配任意复合条件组合，调整条件后即可得到对应结果）

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