带多组AND/OR组合条件的SUMPRODUCT公式正确写法求助

原公式错误原因 #

• SUMPRODUCT 函数内不能直接使用 AND 做数组逐行判断：AND 会对传入的整个数组做全局逻辑判断，仅返回单个布尔值，无法生成逐行判断的布尔数组，导致乘积计算逻辑失效
• 末尾追加的 *ROW(M1:M10) 逻辑与需求无关，属于冗余内容，会直接导致结果计算错误

正确公式（按需求场景选择） #

场景1：统计符合条件的总行数 #

无需数组回车，直接输入即可生效：

=SUMPRODUCT(--(A1:A10=1),--(B1:B10=2),--(C1:C10=3),--((X1:X10=26)*(Y1:Y10=26)+(Z1:Z10=26)>0))

逻辑说明：

• 前三个 --(条件) 用于逐行校验A/B/C列的三个必选条件，满足返回1，不满足返回0
• 后续 (X*Y条件 + Z条件>0) 用于逐行校验可选条件：* 对应逻辑与（判断X、Y是否同时为26），+ 对应逻辑或（判断两个可选条件是否有任意一个满足），外层 >0 用于避免两个可选条件同时满足时求和结果为2，转成布尔值后再用 -- 转为1/0参与乘积计算

场景2：提取所有符合条件的行号（仅Excel 2021/365及以上版本支持） #

直接输入即可自动溢出返回所有符合条件的行号：

=FILTER(ROW(1:10),(A1:A10=1)*(B1:B10=2)*(C1:C10=3)*(((X1:X10=26)*(Y1:Y10=26))+(Z1:Z10=26)>0),"无符合条件的行")

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