计数与求和于一身的SUMPRODUCT函数(一)

1  为什么要使用SUMPRODUCT函数？

不论是COUNTIF函数、COUNTIFS函数，还是SUMIF函数、SUMIFS函数，在进行判断时，工作表必须存在这样的判断区域，否则这几个函数是不能使用的。但是，在实际工作中，原始数据往往是各种各样的，条件隐含在数据中的情况比比皆是。

下图就是这样的一种情况。工作表左边的4列是原始数据，右侧是需要做的汇总报表，按照产品类别和月份进行汇总。

在这个表单数据中，并没有单独的一列来保存产品类别，也没有单独的一列来保存月份（尽管名义上A列是日期，但不是真正的日期），因此，如果要使用SUMIFS函数来求和的话，就必须把C列产品编码的左两位所代表的产品类别分离出来单独保存在一列里，把A列数据中间两位数字取出来转换为月份单独保存另外一列里，这样的操作显然违背了高效数据分析的基本原则。那么，我们能不能在不使用辅助列的情况下，使用一个综合公式来解决呢？答案是肯定的，就是使用SUMPRODUCT函数。

2、SUMPRODUCT函数基本原理

SUMPRODUCT的功能是对多个数组的各个对应的元素进行相乘，然后再把这些乘积相加，语法如下：

=SUMPRODUCT(数组1，数组2，数组3，……)

顾名思义，从函数名字上理解，SUMPRODUCR= SUM + PRODUCT。

在使用这个函数时，要牢记以下重要的两点：

各个数组必须具有相同的维数。

非数值型的数组元素是作为0处理的。例如，逻辑值TRUE和FALSE都被处理成0了，为了把TRUE还原成数字1，把FALSE还原为数字0，可以把它们都乘以1：TRUE*1，FALSE*1）。

下面我们结合2个示例，来解释SUMPRODUCT函数的基本原理和用法。

示例1

下图是一个各个产品销售单价、销售量和折扣率的数据，现在要求计算所有产品的销售总额、折扣额、销售净额。

对于这样的问题，很多人会采用这样的做法：在数据区域的右侧插入2个辅助列，分别计算出每个产品的销售额和折扣额，再使用SUM函数求和。

这里，每个产品的销售额就是每个产品单价和销售量相乘的结果，也就是B列的单价与C列销售量相乘；每个产品的折扣额就是每个产品单价、销售量和折扣率相乘的结果，也就是B列的单价与C列销售量以及D列折扣率相乘的结果。

这种先把几列（或者几行）数据分别相乘，然后再把这些乘积相加的计算问题，Excel给我们提供了一个非常有用的函数：SUMPRODUCT函数。

在这个例子中，利用SUMPRODUCT函数计算所有产品的销售总额、折扣额、销售净额的公式分别如下：

销售总额：

=SUMPRODUCT(B2:B9,C2:C9)

折扣额：

=SUMPRODUCT(B2:B9,C2:C9,D2:D9)

销售净额：

=SUMPRODUCT(B2:B9,C2:C9,1-D2:D9)

示例2

下图是一个评分表，有5个评价指标，每个指标的权重是不同的，现在要计算每个人的评分，而这些评分就是每个指标分数与指标权重相乘相加的合计数，也就是数学中的，那么，计算公式如下。

=SUMPRODUCT($B$2:$F$2,B5:F5)

SUMPRODUCT可以替代COUNT、COUNTA、COUNTBLANK、COUNTIF、COUNTIFS、SUMIF、SUMIFS等函数，其原理就是使用条件表达式构建只有数字0和1组成的数组，然后将这个数组中的所有数字1和0相加，就是满足条件的单元格个数；将这些只有数字0和1的数组与实际求和区域的每个单元格数据相乘相加，就得到满足条件的合计数。

但是，条件表达式的结果是两个逻辑值TRUE和FALSE，而SUMPRODUCT会把这两个逻辑值都当做0处理，因此，需要将条件表达式乘以数字1，或者除以数字1，或者输入两个负号，以使其转换为数字1和0。