Excel中如何让SUM类公式的IF条件自动扩展到更多列？

解决方案 #

完全可以用纯Excel公式实现可拖拽效果，分365/2021及以上版本、旧版本两种情况给出公式：

版本1：Excel 365/2021及以上（支持LAMBDA函数） #

假设你的数据结构为：A列是第1年数据，行范围3~13，第2年的结果单元格是B14，直接在B14输入如下公式即可：

=SUM(BYROW($A$3:A13,LAMBDA(x,OR(x<0)))*IF(B3:B13="-",0,B3:B13))

公式说明

• 混合引用$A$3:A13往右拖拽时会自动扩展范围，比如拖到第3年的结果单元格C14时，范围自动变为$A$3:B13，正好覆盖所有之前年份的对应行数据
• BYROW逐行判断之前年份有没有出现过负数，返回布尔数组
• IF(B3:B13="-",0,B3:B13)把空值的-替换为0，避免计算报错

版本2：旧版Excel（无LAMBDA函数） #

同样在第2年结果单元格B14输入如下数组公式，输入完成后按Ctrl+Shift+Enter生效：

=SUM(IF(MMULT(--($A$3:A13<0),ROW($A$1:INDEX(A:A,COLUMNS($A$3:A3)))^0)>0,IF(B3:B13="-",0,B3:B13),0))

公式说明

• 用MMULT逐行计算之前年份负数出现的次数，只要结果>0就代表该行至少有一年出现过负数，和你之前手动累加条件的逻辑完全一致
• 同样支持直接往右拖拽填充，新增年份无需调整公式逻辑

使用注意 #

1. 第1年的结果固定为0，无需修改
2. 如果你的实际表格数据起始行/列和示例不同，只需要调整公式里对应的行号、列标即可，核心引用逻辑不用改

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