Excel：带可变条件数量的SUMIFS公式实现咨询

原始数据 #

求和需求 #

需要根据可变数量的条件（A/B/C/D列，标记NA的列不参与筛选）对Amount列求和，具体条件如下：

实现方案 #

用SUMPRODUCT完全可以搞定，核心是对每个条件列做分支判断：不筛选的列生成全1数组，筛选的列生成逻辑判断数组，最后和Amount列相乘求和。

公式写法 #

假设原始数据在A2:E5，条件行从第8行开始，以第一行求和单元格E8为例，公式如下：

=SUMPRODUCT(
  --(IF(A8="NA", 1, A2:A5 & A8)),
  --(IF(B8="NA", 1, B2:B5=B8)),
  --(IF(C8="NA", 1, C2:C5=C8)),
  --(IF(D8="NA", 1, D2:D5 & D8)),
  E2:E5
)

公式解释 #

• 不筛选的列：如果条件单元格是NA，IF返回1，生成全1数组，相当于该列所有行都符合条件
• 带运算符的条件：比如>1，用A2:A5 & A8拼接成0>1、0>1、1>1、2>1这类表达式，--把逻辑结果转成1（TRUE）或0（FALSE）
• 等于条件：比如0，直接用B2:B5=B8生成逻辑数组，再转成1/0
• 最后SUMPRODUCT把所有数组对应相乘，再求和，得到符合所有条件的Amount总和

实际结果验证 #

1. 第一行条件：没有数据同时满足A>1、B=0、C=2、D<0，结果为0
2. 第二行条件：没有数据满足A=1且B=2，结果为0
3. 第三行条件：只有第三行数据A=1，求和结果为121

小调整 #

如果你的条件用空值代替NA，只需要把公式里的"NA"改成""就行；要是用其他标记，对应替换即可。

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