EXCEL SUMPRODUCT 高级用法:多条件求和计数、加权计算

大家好，今天我们来聊一个Excel里被严重低估的函数——SUMPRODUCT。

很多人第一次接触它，是在"加权平均"的场景里。但实际上，这个函数远不止于此。它能在很多场景下替代 SUMIFS、COUNTIFS，甚至完成它们做不到的事。今天我们就把 SUMPRODUCT 的高级用法一次讲透。

一、先复习一下基础

SUMPRODUCT 的基本语法：

=SUMPRODUCT(数组1, 数组2, ...)

它的原理是：把各个数组对应位置的元素相乘，再把乘积加总。

举个最简单的例子：

=SUMPRODUCT(A1:A3, B1:B3) 的结果是 2×3 + 4×5 + 6×7 = 6+20+42 = 68。

这是基础。但真正让 SUMPRODUCT 封神的，是它能把条件判断嵌进数组运算里。

因为条件判断（比如 A1:A10="苹果"）会产生一组 TRUE/FALSE，而 SUMPRODUCT 会自动把 TRUE 当作 1、FALSE 当作 0 参与运算。这就是它能做条件求和、条件计数的底层逻辑。

二、多条件求和

先看数据：

需求：求"销售一部"且"产品为苹果"的销量总和。

用 SUMPRODUCT 写：

=SUMPRODUCT((B2:B6="销售一部")*(C2:C6="苹果")*D2:D6)

拆解一下：

• (B2:B6="销售一部") 得到 {TRUE;FALSE;TRUE;TRUE;FALSE}

• (C2:C6="苹果") 得到 {TRUE;FALSE;TRUE;FALSE;TRUE}

• 两个条件相乘，得到 {1;0;1;0;0}——只有同时满足两个条件的行才是 1

• 再乘以销量 D 列，最后加总

结果 = 100 + 150 = 250。

这就是 SUMPRODUCT 做多条件求和的标准写法。注意条件之间用 * 连接，代表"且"（AND）。

如果是"或"（OR）呢？用 +：

=SUMPRODUCT(((B2:B6="销售一部")+(B2:B6="销售二部"))*D2:D6)

=SUMPRODUCT(--((B2:B6="销售一部")+(B2:B6="销售二部")>0), D2:D6)

三、多条件计数

同样的数据，需求：统计"销售一部"且"产品为苹果"的记录数。

=SUMPRODUCT((B2:B6="销售一部")*(C2:C6="苹果"))

结果 = 2（张三、王五两行）。

如果只统计单条件，比如"销售一部"有几条：

=SUMPRODUCT(--(B2:B6="销售一部"))

这里用 -- 把 TRUE/FALSE 转成 1/0。其实也可以写成 (B2:B6="销售一部")*1，效果一样。

为什么计数要用 -- 而不是直接写？ 因为如果只有一个数组参数，SUMPRODUCT 会把 TRUE/FALSE 直接求和，但为了保险和可读性，建议统一用 -- 转换。

四、加权计算

这是 SUMPRODUCT 最经典的场景。

需求：求加权平均单价（总金额 ÷ 总数量）。

=SUMPRODUCT(B2:B4, C2:C4)/SUM(C2:C4)

分子 = 10×100 + 20×50 + 15×80 = 1000+1000+1200 = 3200分母 = 100+50+80 = 230加权平均 = 3200/230 ≈ 13.91

再比如加权得分场景：

如果权重在单独一行，可以这样写：

=SUMPRODUCT(B2:D2, $B$5:$D$5)

其中 B5:D5 是权重 {0.3, 0.3, 0.4}。这样下拉就能算出每个学生的加权总分。

加权计算的核心思想就是：权重数组 × 数值数组，再求和。 SUMPRODUCT 天生就是干这个的。

五、几个进阶技巧

1. 带通配符的条件

SUMPRODUCT 支持通配符，但要注意写法：

=SUMPRODUCT((ISNUMBER(SEARCH("苹果", C2:C6)))*D2:D6)

这里用 SEARCH 查找"苹果"是否出现在产品名中，ISNUMBER 转成 TRUE/FALSE，再参与运算。比直接写 C2:C6="*苹果*" 更可靠（后者在某些版本里不生效）。

2. 区间求和

需求：求销量在 100 到 200 之间的总和。

=SUMPRODUCT((D2:D6>=100)*(D2:D6<=200)*D2:D6)

3. 排除错误值

如果数据里有 #N/A 之类的错误值，SUMIFS 会直接报错，但 SUMPRODUCT 可以用 ISERROR 排除：

=SUMPRODUCT(ISNUMBER(D2:D6)*D2:D6)

只对数值型数据求和，错误值自动跳过。

4. 按月份/年份条件求和

假设 A 列是日期，需求：求 2024 年 3 月的销量。

=SUMPRODUCT((YEAR(A2:A100)=2024)*(MONTH(A2:A100)=3)*D2:D100)

这个 SUMIFS 也能做，但需要配合辅助列或日期区间，SUMPRODUCT 直接一步到位。

六、注意事项（踩坑预警）

1. 数组维度必须一致。所有数组的行列数要相同，否则报 #VALUE!。

2. 不要整列引用。SUMPRODUCT 是数组运算，A:A 这种整列引用会极大拖慢计算速度，建议用具体范围如 A2:A1000。

3. 文本型数字要转换。如果销量列是文本格式的数字，乘法会得到 0，需要先用 -- 或 VALUE 转换。

4. 条件用 * 连接时，括号不能少。(A1:A10="x")*(B1:B10="y") 每个条件都要用括号包起来。

5. SUMPRODUCT 不支持整列引用中的空单元格运算，空单元格在乘法中会被当作 0，一般不影响结果，但要注意逻辑。

七、总结

SUMPRODUCT 的威力在于：它把"条件判断"和"数组运算"结合在了一起。

• 多条件求和：SUMPRODUCT((条件1)*(条件2)*求和列)

• 多条件计数：SUMPRODUCT((条件1)*(条件2))

• 加权计算：SUMPRODUCT(数值数组, 权重数组)

• 复杂条件：配合 ISNUMBER、SEARCH、YEAR、MONTH 等函数嵌套

它比 SUMIFS/COUNTIFS 更灵活，能处理它们处理不了的条件组合，是 Excel 进阶路上必须掌握的函數之一。

下次遇到多条件统计，不妨先想想：能不能用 SUMPRODUCT 一步搞定？