跳到主要内容

SUMPRODUCT 做多条件计数求和

多条件统计，常见答案是 SUMIFS / COUNTIFS。但遇到这些情况它们就不好使了：

  • 条件是"区间"或"排除某些值"；
  • 要对结果做跨表相乘再汇总；
  • 不想写辅助列，也不想按 Ctrl+Shift+Enter。

SUMPRODUCT 能一次干完。

一、从求和说起​

基本形式：

=SUMPRODUCT(数组1,数组2)

规则：逐位相乘，再把乘积相加。

=SUMPRODUCT({1;2;3},{10;20;30})   ' = 1×10 + 2×20 + 3×30 = 140

这就是"单价 × 数量"求和的最短写法：

=SUMPRODUCT(B2:B100,C2:C100)

二、加条件：把布尔值转成 1/0​

比较运算产生 TRUE/FALSE，乘一下就变成 1/0：

=SUMPRODUCT((A2:A100="技术部")*(C2:C100))

A2:A100="技术部" 是一组 1/0，乘 C 列金额，再求和——等价于 SUMIF，但更灵活。

多条件：

=SUMPRODUCT((A2:A100="技术部")*(B2:B100="已付款")*(C2:C100))

条件可以任意多，不用管顺序，也不用管求和列在前在后。

三、数值型条件怎么转​

文字比较天生产出布尔值，但 >100 这类条件如果写成直接比较也能用：

=SUMPRODUCT((B2:B100>100)*(C2:C100))

如果条件来自另一个单元格，注意不要用引号拼接：

=SUMPRODUCT((B2:B100>F1)*(C2:C100))        ' ✅ 直接比较

有人会写成 "&"">"&F1&"" 这类字符串再转换，又长又容易错。直接比较就够了。

四、排除条件​

统计"除技术部以外的金额"：

=SUMPRODUCT((A2:A100<>"技术部")*(C2:C100))

不等于、不包含都好写。排除多个值时用乘法连起来：

=SUMPRODUCT((A2:A100<>"技术部")*(A2:A100<>"财务部")*(C2:C100))

这在 SUMIFS 里得写好几组条件，SUMPRODUCT 一行搞定。

五、区间条件​

按金额分档统计：

=SUMPRODUCT((C2:C100>=1000)*(C2:C100<5000)*(D2:D100))

SUMIFS 也能做多区间，但一旦同时有"包含文本"这类条件，SUMPRODUCT 更好写。

六、更复杂的：含文本判断​

SUMPRODUCT 里可以套 ISNUMBER/SEARCH：

=SUMPRODUCT(--ISNUMBER(SEARCH("北京",E2:E100))*(C2:C100))

统计 E 列文本包含"北京"的金额。SUMIF 支持通配符可以写 "*北京*"，但遇到多个关键词就难了：

=SUMPRODUCT((ISNUMBER(SEARCH("北京",E2:E100))+ISNUMBER(SEARCH("上海",E2:E100))>0)*(C2:C100))

+ 相当于"或"，* 相当于"且"。这个规律记住，多复杂的条件都能拼出来。

七、几个约束必须知道​

  • 不能用整列引用：SUMPRODUCT(A:A,B:B) 会算到 104 万行，慢到卡死。必须写实际范围。
  • 区域大小必须一致：A2:A100 和 C2:C99 会报 #VALUE!。
  • 区域内不能有错误值：只要有一个 #N/A，整个结果就是错误。用 IFERROR 或先清理。
  • 结果的顺序无关：不要求排序，比 LOOKUP 省心。

八、什么时候还是用 SUMIFS​

不是所有场景都该用 SUMPRODUCT：

场景建议
单纯多条件求和SUMIFS，更快
需要排除、包含、区间混合SUMPRODUCT
数据量过十万行SUMIFS 或透视表
要相乘再汇总SUMPRODUCT
条件复杂到要写辅助列SUMPRODUCT + 辅助列都行

性能上 SUMIFS 通常快数倍，因为它是专门优化的。别为了"公式优雅"牺牲掉响应速度。

九、速查​

需求写法
乘积求和=SUMPRODUCT(B2:B100,C2:C100)
单条件求和=SUMPRODUCT((A2:A100="技术部")*C2:C100)
多条件条件之间用 *
或条件条件之间用 +，外面套 >0 或 --
排除(区域<>"值")*
包含文本--ISNUMBER(SEARCH("关键词",区域))

SUMPRODUCT 真正的价值是"把逻辑写成表达式"。一旦你习惯用 * 和 + 表达与或，很多以前要建辅助列的问题都会消失。