SUMPRODUCT：多条件计算的万金油

一个本来用来做乘加的函数

SUMPRODUCT 的官方定义朴素得让人失望：先求多个数组的对应元素乘积，再把所有乘积加起来。=SUMPRODUCT(A2:A5,B2:B5) 等价于 =A2*B2+A3*B3+A4*B4+A5*B5，就是这么简单。它的典型用途是算总金额：数量列乘以单价列，逐行相乘再汇总，一行顶一列辅助列。

可真正让它封神的是另一件事：它天生按数组方式运算，而且不需要 Ctrl+Shift+Enter。在 SUMIFS 还没出现的年代（Excel 2003 及更早），所有多条件求和都必须靠它；即便到了今天，遇到 SUMIFS 处理不了的条件，第一个被想起来的还是它。可以说 SUMPRODUCT 是 Excel 里最典型的“老函数新用”，语法一直没变，能力边界却被人不断往外推。

它的痛点解决得很明确：当一个条件无法用“等于某个值”来表述时，SUMIFS 就束手无策了，而 SUMPRODUCT 几乎什么条件都能写。比如“统计金额大于平均值的订单数”“统计姓名里包含某个字的客户数”“区分大小写地统计编码出现次数”“按月份汇总而不管具体日期”，这些 SUMIFS 要么做不到、要么要绕很大一圈，SUMPRODUCT 却能在一条公式里写清楚。

换个角度理解：SUMPRODUCT 把“条件”变成了普通的算术运算。条件成立是 1，不成立是 0，乘上数值再求和，等于只对成立的那些行求和。一旦看穿这层，你会发现所谓多条件统计，本质上就是一串 0 和 1 的乘法游戏。

还有一点常被忽略：SUMPRODUCT 是少数几个“不需要三键、却天然按数组运算”的函数。在动态数组出现之前，想在一条公式里对整列做判断，唯一舒服的选择就是它。即便到了 Microsoft 365 时代，很多新函数能做的事 SUMPRODUCT 依然能做，而且兼容性更好。所以它在新老版本交替的几年里，始终是最稳的那一块砖。

当然它也不是没有代价。数组运算意味着每一行都会被逐一处理，数据量大时速度会明显下降，这一点后面会专门讲。理解它的运算方式，既能让你写出正确的公式，也能让你提前判断“这个需求该不该交给它”。

另外它还有一个容易被忽略的副作用：因为不需要三键，SUMPRODUCT 写出来的公式可以被任意复制、移动、双击编辑，不会因为丢了数组大括号而失效。对要交接给别人的表格来说，这种健壮性比公式短几行更重要。很多团队把 SUMPRODUCT 写进模板规范，理由就在这里。

运算过程拆解：0 和 1 的乘法游戏

来看一条最经典的公式：=SUMPRODUCT((B2:B100="华东")*(G2:G100))。它做的事情可以拆成四步。

第一步，判断。括号里的 B2:B100="华东" 会对 B 列第 2 行到第 100 行的每一个单元格做一次比较，得到 99 个 TRUE 或 FALSE 组成的数组，比如 {TRUE;FALSE;TRUE;...}。

第二步，转换。TRUE 和 FALSE 不能直接参与乘法，必须变成数字。乘号在这里起了双重作用：既把逻辑值转成 1 和 0，又完成与数值列的相乘。所以 (B2:B100="华东")*G2:G100 得到的是 {G2的值;0;G3的值;...}，条件不成立的行被乘成了 0。

第三步，求和。SUMPRODUCT 把这 99 个乘积加起来，0 不影响结果，最终只剩满足条件的那些金额之和。

第四步，返回。结果是一个普通数字，可以直接参与后续计算。

这里有个容易混淆的细节：参数之间用逗号还是用乘号。写成 =SUMPRODUCT(--(B2:B100="华东"),G2:G100) 也能得到同样结果，区别是逗号形式要求两个参数维度完全一致，而乘号形式允许一个 99 行 1 列去乘一个 1 行 12 列，直接得到 99 行 12 列的矩阵，这个特性在做二维交叉统计时非常有用。日常使用推荐乘号，容错更好，也更容易读懂。

把逻辑值转成数字的办法不止一种。最常用的是双负号 --，连续两个减号第一次取反、第二次取回，顺带完成类型转换；也可以写成 *1 或 +0，效果完全一样。注意单负号不行，会把 TRUE 变成 -1。三种写法里 -- 最流行，但在嵌套较深时容易看漏，此时用 *1 反而更清楚。

顺带说一个调试技巧。想看懂一条长 SUMPRODUCT 到底在算什么，可以选中公式里的某一段，按 F9，Excel 会当场把这一段的计算结果显示出来，比如把 (B2:B100="华东") 单独求值，就能看到一串 TRUE 和 FALSE。看完记得按 Esc 取消，别直接回车，否则公式会被替换成硬编码的结果。这个技巧对所有复杂公式都适用，排查 SUMPRODUCT 时尤其管用。

多条件之间的关系用算术表达：并且（AND）用乘法，或者（OR）用加法。=SUMPRODUCT((区域="华东")*(月份=3)*金额) 表示华东且三月；=SUMPRODUCT(((区域="华东")+(区域="华南"))*金额) 表示华东或华南。用加法时要小心重复计数：如果某一行同时满足两个条件，相加得 2，会被算两次。稳妥写法是在外面套一层判断 =SUMPRODUCT(--(((区域="华东")+(区域="华南"))>0),金额)，只要大于 0 就只算一次。

五类可以直接抄的写法

写法一，多条件求和。订单表 B 列是区域、C 列是月份、G 列是金额，统计华东区三月的销售额：=SUMPRODUCT((B2:B2001="华东")*(C2:C2001=3)*G2:G2001)。三个数组相乘，长度必须一致，都是 2000 行。

写法二，多条件计数。不乘数值列，只把条件相乘再求和：=SUMPRODUCT((B2:B2001="华东")*(G2:G2001>10000))，统计华东区金额大于一万的订单笔数。如果想只统计非空且满足条件的行，再加一个条件 (G2:G2001<>"")。

写法三，加权平均。数量在 D 列、单价在 E 列，算平均单价不能用 =AVERAGE(E2:E100)，那会把一笔大单和一笔小单同等看待。正确写法是 =SUMPRODUCT(D2:D100,E2:E100)/SUM(D2:D100)。这是 SUMPRODUCT 的本职工作，也是财务和采购最常用的一类。

写法四，去重计数。统计客户列里有多少个不重复的名称：=SUMPRODUCT(1/COUNTIF(A2:A500,A2:A500))。原理是每个值出现 n 次就在 COUNTIF 里被数到 n 次，取倒数后每个值贡献 1/n，n 个加起来刚好是 1。这个写法对空单元格会返回 #DIV/0!，改进版是 =SUMPRODUCT((A2:A500<>"")/COUNTIF(A2:A500,A2:A500&""))。

把这五类整理成速查表，贴在工作表旁边的备注区里，用的时候照着改区域就能写对。

写法五，二维交叉查询。行标题在 A 列、列标题在第 1 行、数据在 B2:M100，要查“产品 C 在三月”的数值：=SUMPRODUCT((A2:A100="产品C")*(B1:M1="三月")*B2:M100)。这里 99 行 1 列乘 1 行 12 列，自动扩展成 99 行 12 列，只有交叉点那一个格子不为 0。不需要辅助列，也不需要 INDEX 套 MATCH 套两层。

二维交叉写法在做“行列双向汇总表”时尤其顺手。假设你要做一张 12 行 8 列的矩阵，行是月份、列是大区，格子里填对应的销售额。传统做法要写 96 条 SUMIFS，或者先做透视表再粘成静态数字；用 SUMPRODUCT 只需要在每个格子里写一条 =SUMPRODUCT((MONTH(日期列)=行月份)*(区域列=列大区)*金额列)，靠相对引用拖动就能填满整张表，而且源数据一改全部自动更新。这类矩阵的维护成本几乎为零，是很多人学会 SUMPRODUCT 之后第一个拿去替换老流程的场景。

和 SUMIFS、COUNTIFS 怎么分工

先说结论：能用 SUMIFS 就用 SUMIFS，剩下的交给 SUMPRODUCT。SUMIFS 是专门为多条件求和设计的，语法直观，同行一看就知道在算什么，计算效率也更高，Excel 内部对它做了优化，几万行数据时差距很明显。它的限制主要有三条：条件只能是“区域等于/大于/小于等于某个值或单元格引用”，不能对区域做运算后再比较；不支持区分大小写；不能把函数结果当条件区域，比如“金额大于平均值”这种条件必须先算出平均值放进某个单元格再引用。

碰到下面这几种情况，就该换 SUMPRODUCT 了。条件需要对数据做变换，比如按月份汇总而源数据是完整日期，写 =SUMPRODUCT((MONTH(日期列)=3)*金额列)，SUMIFS 做不到；条件需要区分大小写，写 =SUMPRODUCT(--EXACT(编码列,"AB12"),数量列)；条件需要模糊匹配，写 =SUMPRODUCT(--ISNUMBER(SEARCH("科技",公司名列)),金额列)，注意 SUMPRODUCT 不支持通配符星号问号，得靠 SEARCH 或 FIND 曲线救国；条件跨两张表的行列方向不同，用二维交叉写法。

还有一类是“或”关系比较复杂的情况。SUMIFS 只能表达并且，多个或条件要写好几条公式再相加；SUMPRODUCT 用加号一次搞定，配合外层的大于零判断避免重复计数。条件一多，这个优势会越来越明显。

再补一句关于 COUNTIFS 和 AVERAGEIFS。计数优先用 COUNTIFS，它的效率和可读性都优于 SUMPRODUCT，只有在需要去重计数、区分大小写、或者按月份聚合时才换回来。至于 AVERAGEIFS，它和 SUMIFS 一样只能处理“并且”关系，遇到加权平均、去掉最高最低分之后再平均这类需求，还是得靠 SUMPRODUCT 手动构造。比如去掉一个最高分一个最低分再求平均：=(SUM(G2:G20)-MAX(G2:G20)-MIN(G2:G20))/(COUNT(G2:G20)-2)，看起来没用 SUMPRODUCT，但一旦条件变成“去掉华东区的最高分”，就只能写成 =SUMPRODUCT((区域<>"华东")*金额)/(SUMPRODUCT(--(区域<>"华东"))-1) 这种形式了。

版本兼容性方面 SUMPRODUCT 几乎没有限制，Excel 2003 就有，WPS 也完整支持，是所有函数里最不用担心“同事打不开”的那一类。唯一的遗憾是它不认隐藏行，筛选之后它照样算全部数据，需要跟随筛选就得请出 AGGREGATE，相关对比见AGGREGATE：会跳过错误的统计。

正因为兼容性好、能力边界又宽，SUMPRODUCT 通常被当成兜底方案：新函数不会写、老函数做不到的，先想想它能不能顶上。本书最后一篇整理了一张冷门函数速查表，把每个函数的适用场景和最低版本列在一起，遇到拿不准该用谁的时候可以翻冷门函数速查与实战清单。

翻车提醒

坑一，整列引用。写 =SUMPRODUCT((B:B="华东")*G:G) 看起来很省事，实际会让 Excel 对一百多万行做逐行运算，一个公式就能让文件卡到打不开。永远写明确的范围，并且尽量贴合实际数据行数。如果数据会增长，就把区域转成正式表格（Ctrl+T）后用结构化引用，或者预留一定的余量。

坑二，维度不一致。两个参数行数不同会直接返回 #VALUE!。插入行后区域自动扩展的情况还好，最怕的是手工改区域时只改了一处，比如把 B2:B2001 改成 B2:B3000 却忘了改 G 列。养成同时框选、同时修改的习惯。

坑三，文本型数字。从系统导出的金额常常是文本，参与乘法时 Excel 通常能自动转换，但 ="100">50 这类比较会按文本规则走，结果完全不对。用 =ISTEXT(G2) 抽查一下，有问题就用分列或 =VALUE() 转成真数字。

坑四，区域里混着错误值。SUMPRODUCT 遇到错误值会把错误一路带到底，结果就是 #N/A。要么先用 IFERROR 把源数据清洗成 0，要么换成能跳过错误的 AGGREGATE。

坑五，忘了转逻辑值。写成 =SUMPRODUCT((B2:B100="华东"),G2:G100) 用逗号分隔却不加 --，Excel 会把 TRUE 当成 0 处理，结果恒为 0，而且不报错，最难排查。记住：用乘号连接时不用转，用逗号分隔时必须转。

坑六，空单元格被当成 0。条件写成 =SUMPRODUCT((G2:G100<500)*G2:G100) 时，空格子也满足小于 500，会被计入。想排除空值就补一个条件 (G2:G100<>"")。

坑七，标题行被框进区域。写成 =SUMPRODUCT((B1:B2001="华东")*G1:G2001) 时，第一行的标题文本也参与运算，文本乘文本得到 #VALUE!，整条公式报废。从 B2 而不是 B1 开始框选，是最基础也最容易犯的错。

坑八，日期条件写成文本。写 (A2:A2001="2024-03-01") 比较的是文本而不是日期，永远不成立。正确写法是比较单元格引用，或者用 =SUMPRODUCT((A2:A2001=DATE(2024,3,1))*G2:G2001)。日期在 Excel 里是数字，别把它当字符串。

常见问题

去重计数公式为什么会返回错误

最常见的写法 =SUMPRODUCT(1/COUNTIF(A2:A500,A2:A500)) 有一个前提：区域里不能有空单元格。一旦有空格，COUNTIF 数出来的结果是 0，1 除以 0 就得到 #DIV/0!，整条公式报废。改进版是给分母加个保险，写成 =SUMPRODUCT((A2:A500<>"")/COUNTIF(A2:A500,A2:A500&""))，分子先把空值挡掉，分母里的 &"" 是为了让空单元格也能被 COUNTIF 正确匹配，避免同样出现 0。如果你的版本支持动态数组，其实用 =COUNTA(UNIQUE(区域)) 会清楚得多，只是老版本没有 UNIQUE，这套倒数写法至今仍然是最通用的方案。

为什么公式结果总是 0 却不报错

八成是逻辑值没转成数字。用逗号分隔参数时，TRUE 和 FALSE 不会自动变成 1 和 0，SUMPRODUCT 直接把它们当 0 处理，于是总和为 0。解决办法是在每个条件前面加 --，或者干脆改用乘号连接所有参数。第二个常见原因是条件里的文本带空格，比如源数据里写的是“华东 ”，你写的是“华东”，比较永远不成立。用 =EXACT() 抽查，或者用 TRIM 先把源数据清洗一遍。

能按月份汇总吗，源数据是完整日期

可以，这是 SUMPRODUCT 相对 SUMIFS 最有代表性的优势之一。写法是 =SUMPRODUCT((MONTH(A2:A2001)=3)*G2:G2001)，MONTH 函数对整列日期逐一取月份，得到 1 到 12 的数组，再和 3 比较。想同时限定年份就再加一个条件 (YEAR(A2:A2001)=2024)。注意 MONTH 对空单元格返回 1，如果日期列有空格，会被误算进一月，建议补一个 (A2:A2001<>"") 条件。

公式很长，能不能写得更清楚

能。如果版本支持 LET，可以把每个条件先定义成名字，最后再相乘求和，公式立刻变得可读，具体写法见LET：给长公式里的中间值起名字。不支持 LET 的老版本也有办法：把条件拆到辅助列里，先算出 0 和 1，再对辅助列做一次简单的 SUMPRODUCT。很多老手喜欢辅助列，不是不会写长公式，而是为了三个月后自己还能看懂。

为什么加了“或”条件之后数字变大了

因为加法会让同时满足两个条件的行被计两次。比如 (区域="华东")+(渠道="线上")，一条既在华东又在线上的记录会得到 2，求和时就算了两遍。修正办法是把整个和式套进一次判断：=SUMPRODUCT(--(((区域="华东")+(渠道="线上"))>0),金额列)，只要和大于 0 就只记 1 次。如果确定两类条件互斥，不会有同时成立的行，那直接用加法也没问题，但最好在表格空白处写一句注释说明前提。

几十万行用 SUMPRODUCT 会不会很慢

会。SUMPRODUCT 是逐元素运算，行数翻倍计算量就翻倍，而且它不像 SUMIFS 那样有内部优化。经验值是：一万行以内随便用，五万行开始有感觉，十万行以上明显卡顿。优化手段按优先级排列：优先改用 SUMIFS 或数据透视表；其次把区域缩到实际范围，别整列引用；再者把中间结果落到辅助列，避免在一条公式里重复计算同一个数组；最后考虑开启手动重算，改完数据再按 F9。真到了百万行级别，就该考虑把数据搬进 Power Query 或者数据库了。