如何用SUMPRODUCT+SUMIFS计算列A中不在列S指定范围的M列和？

已知条件 #

• 列A数据：

  Jon
  Tina
  Dan
  Mj
  Tyler
  Tim
  Sarah

• 列S（S615:S617）数据：

  Dan
  Sarah
  Ron

• 已验证有效公式（计算A列在S615:S617范围内的M列和）：=SUMPRODUCT(SUMIFS(M:M, A:A, S615:S617))

两种可行解决方案 #

方案1：用M列总和减去符合条件的和

这是最直观的方法，直接基于已验证的公式做差值计算：

=SUM(M:M) - SUMPRODUCT(SUMIFS(M:M, A:A, S615:S617))

优点：逻辑简单，依赖已确认正确的公式，出错概率低。

方案2：直接构造反向匹配公式

通过COUNTIF判断A列值是否不在S615:S617范围内，结合SUMPRODUCT完成求和：

=SUMPRODUCT(M:M, --(COUNTIF(S615:S617, A:A)=0))

• 逻辑说明：COUNTIF(S615:S617, A:A)返回A列每个值在S区域的出现次数，等于0即表示该值不在目标范围内；
• --的作用是将布尔值（TRUE/FALSE）转换为数值1/0，确保只有符合反向条件的行才会将M列值纳入求和。

为什么直接用NOT函数无效？ #

SUMIFS的条件参数不支持直接对数组使用NOT逻辑，比如SUMIFS(M:M, A:A, NOT(S615:S617))的写法不符合函数参数规则，无法正确解析反向匹配的数组条件，因此需要通过上述两种方式绕开这个限制。

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