Google Sheets中SUM结合ARRAYFORMULA、SUMIF使用不等于运算符异常问题

错误原因说明 #

你原来的不等于运算符公式出错的核心原因是：当SUMIF的匹配条件为多元素数组时，公式会对数组内的每一个关联任务ID，分别执行「匹配所有不等于该ID的任务并求和」操作，最后把所有求和结果相加。假设你有N个关联任务ID，所有任务总工时为S，关联项目总工时为S1，最终得到的结果就是N*S - S1，和你想要的「排除所有关联ID后的剩余工时」逻辑完全不符，所以才会出现结果严重偏大的问题。

可行方案 #

方案1：总工时减关联项目工时（最简单易用）

直接用所有任务的总工时减去你已经验证正确的关联项目工时，逻辑最简单，出错概率最低：

=SUM('Sheet1'!C5:C) - SUM(ARRAYFORMULA(SUMIF('Sheet1'!B5:B;"="&{Sheet2!A2:B};'Sheet1'!C5:C)))

方案2：直接过滤未关联任务求和

如果需要通过匹配逻辑直接统计，可以使用SUM+FILTER+COUNTIF的组合公式：

=SUM(FILTER('Sheet1'!C5:C,COUNTIF({Sheet2!A2:B},'Sheet1'!B5:B)=0))

公式逻辑：COUNTIF会统计Sheet1每行的任务ID在Sheet2关联ID列表中的出现次数，次数为0即代表该任务不属于两个关联项目，用FILTER筛选出这部分任务的工时后求和即可得到预期结果。

如果你的Sheet2关联ID列表中存在空值，可以调整公式排除空值避免误判：

=SUM(FILTER('Sheet1'!C5:C,COUNTIF(FILTER({Sheet2!A2:B},{Sheet2!A2:B}<>""),'Sheet1'!B5:B)=0))

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