摘要：它既能搞定简单的乘积累加，又能解决复杂的多条件统计，一个函数替代多个函数，这才是真正的高效办公利器。
它既能搞定简单的乘积累加，又能解决复杂的多条件统计，一个函数替代多个函数，这才是真正的高效办公利器。
在日常工作中，我们经常需要对Excel数据进行复杂的计算和分析。很多时候，我们会使用多个函数组合来解决复杂问题，但有一个函数往往被低估——它就是SUMPRODUCT函数。
这个函数不仅能够简化公式，还能解决许多常规函数难以处理的问题。
今天，让我们一起来探索SUMPRODUCT函数的强大功能，解锁更多实用技巧。
SUMPRODUCT函数的基本功能是将多个数组间对应的元素相乘，然后返回这些乘积的总和。
它的语法结构很简单：=SUMPRODUCT(array1, [array2], [array3], ...)
其中，array1是必需的参数，后续数组是可选的。你可以输入2到255个数组参数。
最基础的应用是乘积求和：假设你有商品单价和数量两列数据，需要计算总销售额。传统方法需要新增一列辅助列计算每个商品的销售额，然后再求和。而SUMPRODUCT函数可以一步到位：=SUMPRODUCT(B2:B5, C2:C5)，这就相当于计算了=B2*C2 + B3*C3 + B4*C4 + B5*C5。
注意事项：
1. 单条件求和
问题：统计特定产品的总销售额。
公式：=SUMPRODUCT((A2:A100="产品A")*(B2:B100))
解析：(A2:A100="产品A")部分会生成一个由TRUE和FALSE组成的数组。在计算时，TRUE被当作1，FALSE被当作0。这个数组与B2:B100范围内的值相乘，只有产品名称为“产品A”的行会保留其销售额，其他行都变为0。
2. 多条件求和
问题：统计特定月份和地区的销售额。
公式：=SUMPRODUCT((A2:A100="1月")*(B2:B100="北京")*(C2:C100))
解析：这个公式设置了两重条件，只有同时满足月份为“1月”且地区为“北京”的行，才会将其销售额计入总和。这种方法可以扩展为三个甚至更多条件，只需继续添加条件判断即可。
3. 单条件计数
问题：统计特定部门的人数。
公式：=SUMPRODUCT(--(A2:A100="行政部"))
解析：与求和不同，计数只关心满足条件的行数。(A2:A100="行政部")生成TRUE/FALSE数组，前面的--（双负号）用于将逻辑值转换为数字1和0。也可以使用N函数实现相同效果：=SUMPRODUCT(N(A2:A100="行政部"))。
4. 多条件计数
问题：统计特定部门中工资高于平均水平的员工数。
公式：=SUMPRODUCT((A2:A100="行政部")*(B2:B100>5000))
解析：两个条件分别生成0/1数组，对应位置相乘后，只有同时满足两个条件的行会保留1，其他都变为0，最后求和即为满足条件的行数。
5. 处理日期数据：按月份汇总
问题：根据日期列统计各月数据。
公式：=SUMPRODUCT((MONTH(A2:A100)=1)*(B2:B100))
解析：这个公式会统计1月份的所有数据。MONTH函数提取日期中的月份信息，然后与指定月份比较。这种方法特别适合处理时间序列数据，可以轻松实现按年、按月或按季度汇总。
1. 中国式排名
问题：实现不跳跃的排名（如并列第5后接着第6名，而不是第6名）。
公式：=SUMPRODUCT(($B$2:$B$100>B2)/COUNTIF($B$2:$B$100,$B$2:$B$100))+1
解析：这个公式避免了RANK函数在遇到相同值时的排名跳跃问题。B2:B100>B2部分计算比当前单元格值大的唯一值的个数，最后+1得到当前值的排名。
2. 模糊条件求和（含关键词求和）
问题：统计包含特定关键词的条目之和。
公式：=SUMPRODUCT(ISNUMBER(FIND("关键词",A2:A100))*(B2:B100))
解析：FIND函数查找关键词位置，找到返回数字，否则返回错误。ISNUMBER将找到关键词的位置转换为TRUE，未找到转为FALSE。这个组合可以筛选出包含特定关键词的行。
3. 处理文本数字混合数据
问题：当数据中混合文本和数字时（如"100元"），需要进行提取和计算。
公式：=SUMPRODUCT(--SUBSTITUTE(A2:A100,"元",""), B2:B100)
解析：SUBSTITUTE函数移除非数字字符（如"元"），--（双负号）将文本数字转换为真实数值，然后进行乘积累加。
4. 跨列统计
问题：根据首行的条件标识，统计对应列的数据。
公式：=SUMPRODUCT(($B$1:$G$1="计划")*(B2:G2))
解析：这个公式特别适合用于计划与实际对比的表格。B1:G1="计划"部分识别表头中的"计划"列，然后与对应行的数据相乘。
5. 加权平均值计算
问题：计算考虑权重的平均值，如学生成绩的综合评分。
公式：=SUMPRODUCT(B2:B100, C2:C100)/SUM(C2:C100)
解析：SUMPRODUCT部分计算各数值与权重的乘积之和，除以权重总和得到加权平均值。这种方法比先计算每个乘积再求和最后除法的传统方法更简洁。
SUMPRODUCT函数可以与其他函数结合，实现更复杂的计算。例如，你可以将条件判断与数学函数、文本函数、日期函数等结合，创建出强大的公式。
与IF函数结合：虽然SUMPRODUCT本身可以处理条件判断，但有时与IF函数结合可以解决更特殊的问题。
数组公式的替代品：很多数组公式可以实现的功能，都可以用SUMPRODUCT代替。SUMPRODUCT公式不需要按Ctrl+Shift+Enter输入，计算效率也更高。
提高计算效率的方法：
避免使用整列引用（如A:A），尽量使用精确的范围（如A2:A100）在多个条件判断时，将最容易失败的条件放在前面，减少计算量在大数据集中，考虑使用Excel表格的结构化引用常见错误及解决方法：
VALUE!错误：通常是因为数组维度不一致，检查所有数组是否具有相同的行数和列数结果总是返回0：可能是因为逻辑值没有转换为数字，尝试使用--或N函数转换计算效率低：可能是引用范围过大，缩小范围或使用动态范围SUMPRODUCT vs SUMIFS/COUNTIFS：
虽然SUMIFS和COUNTIFS在某些情况下更简洁，但SUMPRODUCT有以下优势：
支持更复杂的条件组合（如OR逻辑）可以对不同区域进行运算和比较可以使用其他函数处理条件在旧版Excel（2003以前）中也可用第一题：如何用SUMPRODUCT函数统计"销售部"员工中工资超过8000元的人数？
第二题：现有一组混合单位的数据（如"100元"），如何用SUMPRODUCT计算总和？
第三题：如何使用SUMPRODUCT函数实现中国式排名，避免名次跳跃？
第一题答案：=SUMPRODUCT((A2:A100="销售部")*(B2:B100>8000))
解析：这是多条件计数的典型应用，两个条件同时满足的行会被计数。
第二题答案：=SUMPRODUCT(--SUBSTITUTE(A2:A100,"元",""))
解析：使用SUBSTITUTE移除单位文本，再用--将文本数字转为真实数值，最后求和。
第三题答案：=SUMPRODUCT(($B$2:$B$100>B2)/COUNTIF($B$2:$B$100,$B$2:$B$100))+1
解析：这个公式计算比当前单元格值大的唯一值的个数，实现不跳跃的中国式排名。
每天五分钟，祝你超越80%excel用户！请一定要点赞评论收藏哦！
来源：千万别学Excel
