SUMPRODUCT：不用数组也能多条件

它到底在做什么

SUMPRODUCT 的名字直译过来是“求和乘积”，做的事也确实就是它字面上的意思：把若干个尺寸相同的区域按位置一一对应相乘，再把所有乘积加起来。语法是 =SUMPRODUCT(区域1, [区域2], [区域3], ...)。

最简单的例子：B 列是数量，C 列是单价，要算总金额，传统做法是先加一列算每行小计，再求和。用 SUMPRODUCT 一步到位：

=SUMPRODUCT(B2:B10,C2:C10)

它等价于 B2*C2 + B3*C3 + …… + B10*C10，但不需要任何辅助列。这一条已经能省掉日常报表里相当多的中间列。

真正让它成为“不用数组也能多条件”的关键，是它对真假值的处理。在 Excel 里，逻辑值 TRUE 参与算术运算时等于一，FALSE 等于零。于是 (A2:A10="华东") 这样的判断，会返回一组由 TRUE 和 FALSE 组成的数组，拿它去乘以金额列，符合条件的行乘以一保留原值，不符合的乘以零被抹掉，最后求和就是条件求和。

=SUMPRODUCT((A2:A10="华东")*C2:C10)

这一行公式等价于 SUMIFS(C2:C10,A2:A10,"华东")，但它的能力远不止于此。

在老版本 Excel 里，同样的需求通常要写成 =SUM((A2:A10="华东")*C2:C10) 并按三键确认，公式栏里会出现一对大括号。那种写法叫作数组公式，一旦别人双击单元格再回车，很容易不小心把三键状态弄丢，公式就废了。SUMPRODUCT 没有这个隐患，它天生按数组方式计算，普通回车就能出结果，也不怕别人误编辑。这就是为什么很多人把它叫做不用三键的数组公式。

这张表基本概括了两者各自的舒适区。简单来说：规整的条件求和优先用 SUMIFS，因为它更快、可读性更好；凡是 SUMIFS 表达不了的，比如或条件、按函数结果分组、加权平均、跨列相乘，就交给 SUMPRODUCT。

需要提醒的是，SUMPRODUCT 不需要按三键确认，它天生支持数组运算，这也是标题里说“不用数组也能多条件”的原因。在老版本 Excel 里，它几乎是唯一一个不需要三键就能做多条件统计的函数。

另一个值得先说清楚的细节：当求和区域里存在文本时，逗号写法会把它当零跳过，乘号写法则直接报错。所以拿到一份来源不明的数据，先用 ISNUMBER 抽查求和列，比事后对着错误值发呆要高效得多。类型判断的常用手法在 信息函数：ISERROR、ISNUMBER、ISBLANK 里有系统整理，数据清洗的步骤则参见 文本清洗：TRIM、CLEAN、SUBSTITUTE、REPLACE。

规整的日期区间统计虽然也能用 SUMPRODUCT 实现，但写法不如专用函数直观，建议直接看 SUMIFS 多条件统计：日期区间加多字段，两篇对着读，就能清楚地知道什么时候该换工具。

真假值怎么参与运算

这是理解 SUMPRODUCT 的第二个关键点，也是最容易卡住的地方。当一个 SUMPRODUCT 里只有一个参数时，逻辑值不会被自动转换：

=SUMPRODUCT(A2:A10="华东")

这个公式的结果永远是零，因为 SUMPRODUCT 遇到纯逻辑值数组时不会把它当成数字。必须做一次强制转换，让 TRUE 变成一、FALSE 变成零。有三种常见做法：

=SUMPRODUCT(--(A2:A10="华东"))
=SUMPRODUCT((A2:A10="华东")*1)
=SUMPRODUCT(N(A2:A10="华东"))

第一种最常用，两个负号的写法叫做“双负号”，第一个负号把 TRUE 变成负一，第二个负号再变回正一，顺便完成了类型转换。第二种最直观，乘以一即可。第三种用了 N 函数，专门把逻辑值转成数字，可读性最好但敲起来慢。

好消息是，当公式里还有其他数值区域参与乘法时，转换会自动发生，不需要额外写双负号。比如 =SUMPRODUCT((A2:A10="华东")*C2:C10) 里，乘法运算本身就完成了转换，写得出来就跑得通。只有“纯计数”场景才需要显式加双负号。

且关系用乘法

多个条件同时满足，用乘号连接。因为只有所有判断都为真时，乘积才是一：

=SUMPRODUCT((A2:A100="华东")*(B2:B100="A")*C2:C100)

或关系用加法

满足任一条件即可，用加号连接。要注意的是，如果两个条件同时成立，相加会得到二，导致重复计算。处理办法是外套一层判断，大于零就取一：

=SUMPRODUCT(--(((A2:A100="华东")+(A2:A100="华南"))>0),C2:C100)

如果确定两个条件互斥（比如同一列的不同取值），就不会重复，可以省掉这层判断，直接写成 =SUMPRODUCT(((A2:A100="华东")+(A2:A100="华南"))*C2:C100)。

取反用减号

统计不满足某条件的数据，用一减去判断结果：

=SUMPRODUCT((1-(A2:A100="已取消"))*C2:C100)

或者更直观地写成 =SUMPRODUCT((A2:A100<>"已取消")*C2:C100)。后者可读性更好，推荐优先使用。

SUMPRODUCT 能做而 SUMIFS 做不到的几件事

按函数结果分组

SUMIFS 的条件只能是“区域与常量比较”，没法写“区域的月份等于三”。而 SUMPRODUCT 里放任何函数都行：

=SUMPRODUCT((MONTH(A2:A100)=3)*C2:C100)

同理，按季度、按星期、按年份尾号、按字符串长度分组，都可以在 SUMPRODUCT 里一步完成：

=SUMPRODUCT((YEAR(A2:A100)=2023)*C2:C100)
=SUMPRODUCT((ROUNDUP(MONTH(A2:A100)/3,0)=2)*C2:C100)
=SUMPRODUCT((WEEKDAY(A2:A100,2)>5)*C2:C100)

最后一行统计的是所有周末产生的销售额，这在分析门店客流时非常有用。SUMIFS 要实现同样的效果，只能先加一列辅助列算出月份，再按辅助列统计，多一步就多一分出错的可能。

加权平均

这是 SUMPRODUCT 最经典的应用，没有之一。算平均单价时，简单的 AVERAGE 会把每一笔订单等权看待，正确的做法是按数量加权：

=SUMPRODUCT(B2:B100,C2:C100)/SUM(B2:B100)

其中 B 列是数量，C 列是单价。分子是总金额，分母是总数量，相除得到加权平均单价。这个公式在采购分析、库存计价、投资组合收益率计算里都会用到。

跨列相乘求和

除了开头的数量乘单价，它还常用于算完成率类的指标。比如 B 列是计划数，C 列是完成率，要算加权完成率：

=SUMPRODUCT(B2:B100,C2:C100)/SUM(B2:B100)

模糊匹配

SUMPRODUCT 配合 ISNUMBER 和 SEARCH，可以实现 SUMIFS 通配符做不到的复杂匹配。统计备注里包含“加急”两个字的订单金额：

=SUMPRODUCT(--ISNUMBER(SEARCH("加急",D2:D100)),C2:C100)

SEARCH 找不到时返回错误值，用 ISNUMBER 判断是否为数字，得到一组真假值，再用双负号转成数字。SEARCH 不区分大小写，如果需要区分，换成 FIND 即可。

统计不重复的个数

这是一个没有 SUMPRODUCT 就很难完成的经典需求。A 列列出了每一笔订单的客户名，同一个人会出现多次，要统计一共有多少个不同的客户：

=SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100))

原理很巧妙：COUNTIF(A2:A100,A2:A100) 会为每一个单元格分别统计它自己出现了几次，返回一组由出现次数构成的数组。某个客户出现了三次，那三行各自得到的都是三；取倒数之后每行是三分之一，三行相加正好等于一。于是每个不同的值最终贡献一，求和就是不重复个数。

这个公式有三个使用前提。第一，区域里不能有空单元格，否则 COUNTIF 对空值的统计会导致除零错误。第二，区域里不能有错误值。第三，必须严格限定区域范围，不能写整列。

需要带条件的去重计数时，把 COUNTIF 换成 COUNTIFS 即可：

=SUMPRODUCT((B2:B100="华东")/COUNTIFS(A2:A100,A2:A100,B2:B100,B2:B100))

上面统计的是华东地区有多少个不同客户。分子用地区判断筛出目标行，分母按客户加地区双条件计数，逻辑与基础版一致，只是分母换成了多条件版本。

忽略错误值求和

如果数据区域里混着 #N/A 之类的错误值，SUM 和 SUMIFS 都会直接报错，而 SUMPRODUCT 配合 IFERROR 可以绕过：

=SUMPRODUCT(IFERROR(C2:C100,0))

这个写法在老版本里需要按三键确认。在新版本里可以直接回车。它比先清理错误值再求和要省事得多，尤其是在数据来自外部链接、错误值会定期出现的情况下。

真实场景与性能提示

场景：多条件的动态看板

假设明细表有日期列 A、地区列 B、销售员列 C、金额列 D，参数区在 H1 到 H3 分别放了地区、销售员、月份。做一个随参数变化的总额：

=SUMPRODUCT(($B$2:$B$100=$H$1)*($C$2:$C$100=$H$2)*(MONTH($A$2:$A$100)=$H$3)*($A$2:$A$100>=DATE(2023,1,1))*$D$2:$D$100)

五个条件相乘，涵盖了文本匹配、函数分组、日期边界三类需求，SUMIFS 做不了中间那个 MONTH 判断。

如果希望某个参数留空时表示“不限”，可以在每个判断外面加一层：

=SUMPRODUCT((($H$1="")+($B$2:$B$100=$H$1))*(($H$2="")+($C$2:$C$100=$H$2))*$D$2:$D$100)

这里用 ($H$1="") 判断参数是否为空，为空时结果是 TRUE 也就是一，加一之后至少为一，再乘以其他条件就不会把数据筛掉。这是做动态看板时非常实用的一个小技巧，能让一张表应对各种筛选组合。

场景：按权重打分

招聘或评标时经常需要按权重算总分：B 列到 E 列是四个维度的得分，权重分别是零点三、零点三、零点二、零点二。

=SUMPRODUCT(B2:E2,{0.3,0.3,0.2,0.2})

花括号里的是数组常量，用逗号分隔表示横向排列，分号分隔表示纵向。这个写法比写四个乘法再相加清爽得多，改权重时只改一行。

如果要对所有人一次性算完，可以用矩阵形式：=SUMPRODUCT(B2:E10*TRANSPOSE(权重列))，不过这种写法需要三键确认，日常用得不多，逐行填充上面的写法更省事。

场景：配合条件格式做整行高亮

SUMPRODUCT 不只用于算出数字，它返回的值也可以作为条件格式的判断依据。比如要把“华东地区且金额大于一万”的整行标成黄色，可以选中明细区域，新建条件格式规则，选择使用公式，然后写：

=SUMPRODUCT(($B2="华东")*($D2>10000))>0

注意这里用的是混合引用 $B2 和 $D2，列锁定而行不锁定，这样规则应用到整片区域时，每一行都会拿自己那一行的数据去判断。判断结果为真就触发格式，整行随之高亮。

这个技巧的好处是不需要在表里额外加标记列，规则集中管理，改条件时只改一处。缺点是大表上刷新会稍慢，几千行以内完全无感。

性能提示

SUMPRODUCT 的计算方式是逐行遍历，数据量越大越慢。在几千行以内几乎无感，超过五万行就会有明显延迟，尤其是当公式被复制到几百个单元格时，整张表的重算可能会卡上好几秒。

三条优化建议。第一，能用 SUMIFS 的场景优先用 SUMIFS，它的内部实现经过优化，速度通常快好几倍。第二，区域不要写整列，限定到实际数据范围，比如 A2:A5000 而不是 A:A。第三，如果一张表里放了上百个 SUMPRODUCT，考虑改用数据透视表，透视表是专门为多条件汇总设计的，速度和可维护性都更好。

还有一个折中方案值得掌握：用辅助列把复杂的函数分组结果先算出来，再让 SUMIFS 去统计。比如要在上百个单元格里按月份汇总，与其写一百条 SUMPRODUCT((MONTH(...)=3)*...)，不如加一列 =MONTH(A2) 算出月份，再用 =SUMIFS(D:D,E:E,3) 统计。虽然多了一列，但重算速度可能快十倍以上，公式也更好懂。

判断要不要拆辅助列，有个简单的经验：同一段复杂判断如果在公式里出现超过十次，就该考虑把它固化成一列。这既是为了速度，也是为了日后维护时不用在几十个地方同步修改同一段逻辑。

版本兼容性

SUMPRODUCT 从 Excel 2003 起就存在，WPS 表格同样完整支持，是最不用担心兼容性的函数之一。它的行为在 Excel 与 WPS 中完全一致，包括逻辑值转换、文本视为零、区域尺寸校验等细节。

需要留意的一点：区域尺寸必须严格一致。写 =SUMPRODUCT(A2:A100,B2:B50) 会直接返回 #VALUE!，因为两个区域行数不同。这和 SUMIFS 的要求是一样的，但 SUMPRODUCT 报错更频繁，因为它的公式里常常同时出现多个区域，改其中一个忘了改其他的就会出错。

在 WPS 里使用数组常量时要注意，花括号是手工输入的还是系统自动加的不能搞混，数组常量必须手工敲进去，不能靠三键生成。

常见问题

为什么 SUMPRODUCT 的结果总是 0

最常见的原因是忘了把逻辑值转成数字。当公式里只有判断、没有数值区域参与乘法时，SUMPRODUCT 不会把 TRUE 当一处理，结果就是零。修法是在判断外面加双负号或者乘以一：

=SUMPRODUCT(--(A2:A100="华东"))

第二个原因是区域里全是文本。如果求和区域里的数据是从系统导出的文本型数字，SUMPRODUCT 会把它们当零处理，结果同样是零或者偏小。用 ISNUMBER 抽查一下数据类型，必要时先做转换。

第三个原因是条件写错了，比如比较的是合并单元格、多了空格、或者区域起点错了一行。排查办法是把判断单独写在一个单元格里，看它返回的是 TRUE 还是 FALSE。

用乘号和用逗号有什么区别

两种写法在数值计算上等价，但对文本和逻辑值的处理不同。用逗号分隔时，SUMPRODUCT 会把区域里的文本当成零处理，不会报错：

=SUMPRODUCT(--(A2:A100="华东"),C2:C100)

用乘号连接时，如果区域里有文本，乘法运算会直接返回 #VALUE!：

=SUMPRODUCT((A2:A100="华东")*C2:C100)

所以，当你的数据区域里可能混着文本（比如备注列、尚未填写的金额列）时，用逗号写法更稳健。而当数据确定干净时，乘号写法更短也更常见。

如果数据里还可能有错误值，两种写法都会报错，需要外套 IFERROR 处理，写成 =SUMPRODUCT(IFERROR(--(A2:A100="华东")*C2:C100,0)) 并按三键确认。

怎么用它做多条件计数

把求和区域去掉，只保留判断并用双负号转换即可。统计华东地区且产品为 A 的记录条数：

=SUMPRODUCT((A2:A100="华东")*(B2:B100="A"))

因为两个判断相乘的结果本身已经完成了类型转换，不需要额外加双负号。如果只有一个条件，则需要：=SUMPRODUCT(--(A2:A100="华东"))。

这个写法等价于 =COUNTIFS(A2:A100,"华东",B2:B100,"A")，但当条件里包含函数运算时 COUNTIFS 就无能为力了，比如统计三月份的记录条数：=SUMPRODUCT(--(MONTH(A2:A100)=3))。

SUMPRODUCT 和 SUMIFS 该选哪个

判断标准有三条。第一，条件是否规整：如果都是“某列等于某个值”或者“某列在某个区间”，优先 SUMIFS，它更快也更易读。第二，是否需要或条件、函数分组、加权计算：只要占了一条，就只能用 SUMPRODUCT。

第三，看数据量和公式数量。如果只有几条公式、几千行数据，两者随便选，差异几乎感觉不到。如果是上万行数据加几百条公式，SUMIFS 的速度优势会非常明显，值得为它多写几列辅助列。

最后提醒一句：SUMPRODUCT 的公式一旦写长，可读性会迅速下降。超过三四个条件时，建议拆成几列辅助列分步计算，既方便检查，也方便三个月后的你自己看懂。

为什么公式提示 #VALUE!

三个高频原因。第一，区域尺寸不一致，比如一个区域是九十九行另一个是五十行，检查所有区域的起止行号是否对齐。第二，区域里混着真正的错误值，比如 #N/A、#DIV/0!，这类值会让整个结果报错，需要用 IFERROR 包裹或者先清理源数据。

第三，在旧版本 Excel 里配合 IFERROR 等返回数组的函数使用时没有按三键确认。如果公式看起来正确却报错，重新进入编辑状态按 Ctrl + Shift + Enter 再试一次，很多时候问题就解决了。