Excel 中 SUMPRODUCT 函数的使用技巧

Excel 里难度最大的，应该就是函数公式了。

举个例子：

上面这个公式，SUM函数很简单吧，相信大家都知道是求和。

结果口算都能算出来，是 10。

这个函数又是什么意思呢？

部分同学这会儿就答不上来了。

其实非常简单，SUM是相加，PRODUCT是相乘，

所以计算结果就是把所有的数字相乘：24。

你肯定会表示不服：我就是不知道而已，知道的话肯定立马就答出来了。

没关系，再增加一点难度：

现在你已经学会了SUM和PRODUCT函数，那把这两个函数结合到一起，计算结果应该是什么？

答：结果是 10。

为什么呢？

这节课，我们就一起来学一学SUMPRODUCT这个简单又复杂的函数！

基本用法

SUMPRODUCT 的作用，就是把两列数据相乘，之后再相加。

比如上面的计算结果，就是两列每个单元格相乘，然后相加，结果是 40。

所以这个效果通常会用来计算两列需要相乘的相加的场景，

比如说销量和产品单价：

计算总的销售额，一个 SUMPRODUCT 公式搞定：

=SUMPRODUCT(E2:E10,F2:F10)

有高手领路，是不是超级简单！

SUMPRODUCT 的参数如下：

- 参数 1，要相乘的第 1 列数据；

- 参数 2，要相乘的第 2 列数据；

- 参数 3，依次类推，最多可以选择 255 列数据。

福利小技巧

再送两个福利小技巧：

▋参数相乘效果一样

下面两个公式的结果是一样的，本质上都是两列数据相乘：

当出现错误值的时候，继续在「研发部」里看看能不能找到 ——

=SUMPRODUCT(E2:E10,F2:F10)=SUMPRODUCT(E2:E10*F2:F10)

原理和下面 SUM 函数内相加是一样的。

=SUM(1,2,3)=SUM(1+2+3)

▋单列数据即相加

如果只有 1 列参数，那么效果和用 SUM 函数是一样的。

所以，第 1 个案例为什么是 10，你应该明白了吧，因为只有一列数据。

▋乘以数字

再来一个案例。

现在老板觉得产品单价太低了，每个产品单价都要增加 5 块钱，那么总销售额应该怎么算？

不用每个单价都加上 5，直接一个 SUMPRODUCT 公式搞定。

=SUMPRODUCT(E2:E10,F2:F10+5)

看见了吗？很简单，直接在求和的列当中加上 5 就可以了。

这个计算过程，实际上是对这个区域每个单元格逐一加了 5：

所以，加一个 5，和加一列 5 本质是一样的。

涨 5 块觉得还是不够高，直接价格翻倍乘以 2，应该怎么算？

公式是这样的：

=SUMPRODUCT(E2:E10,F2:F10*2)

他的计算原理和加号是一样的，如下，

▋条件计数

毕竟是 SUM 和 PRODUCT 两个函数的合体，SUMPRODUCT 用法还不止这些。

看下面这一个问题，想要统计每个部门的人数，公式是这样的：

=SUMPRODUCT((B2:B10=G2)*1)

思路解析：

B2:B10=G2，其实就是看看B2、B3、……、B10单元格，是不是和 G2，也就是「销售一部」长的一样。

如果一样，则为true，也就是 1，那么得出的结果为 1；

如果不一样，则为false，也就是 0，那么得出的结果为 0。

最后加起来就是符合条件的数量啦。

如果有多个条件，就再乘以这个条件判断的逻辑值。比如要判断每个部门女生数量：

公式如下：

=SUMPRODUCT((B2:B10=G2)*(D2:D10="女"))

再比如，要统计 2010 年以前入职的每个部门女生数量，再乘以一个条件值：

公式是这样的：

=SUMPRODUCT(B2:B10=G2)*D2:D10="女")*YEAR(E2:E102010)

用法你总结出来了吗？

一句话，有多少个条件就乘以多少次：

▋条件求和

就这样而已吗？SUMPRODUCT 肯定不服。

现在我们要统计每个部门的销售量，

对应的 SUMPRODUCT 公式是这个样子：

=SUMPRODUCT($E$2:$E$10*$F$2:$F$10*($B$2:$B$10=H2))

计算过程是这样的：

如果有多个条件，比如计算每个部门「产品 C」的销售额：

公式如下：

=SUMPRODUCT(($E$2:$E$10)*($F$2:$F$10)*($B$2:$B$10=H2)*(D2:D10="产品C"))

和前面的计数原理一样，把数据列和条件列乘起来就可以了。

写在最后

到这里你可能会有点疑惑：

这些计算，用 SUMIFS 和 COUNTIFS 都可以实现啊，为什么还要用 SUMPRODUCT 啊？

原因不是明摆着的吗？一个函数顶四个！

要知道，条件数量不一样，你可能要在 SUMIF、SUMIFS、COUNTIF 和 COUNTIFS 之间来回切换，

而 SUMPRODUCT 只用一个函数就可以了。

另外 SUMPRODUCT 用逻辑运算符直接比较判断，公式逻辑更加的清晰。

更重要的，它还可以计算更复杂的条件判断。