计数和求和相结合的SUMPRODUCT函数(一)

1为什么要使用SUMPRODUCT功能？

无论是COUNTIF函数、COUNTIFS函数、SUMIF函数还是SUMIFS函数，在判断时工作表中一定要有这样的判断区域，否则这些函数都无法使用。但在实际工作中，原始数据往往千差万别，条件处处隐藏在数据中。

下图就是这样的情况。工作表左侧四列为原始数据，右侧需要做汇总报表，按产品类别、月份汇总。

在这个表单数据中，没有单独的列保存产品类别，也没有单独的列保存月份(A列虽然名义上是日期，但不是真实日期)。因此，如果要用SUMIFS函数求和，就必须把C列产品代码左两位数字代表的产品类别分开，分别保存在一列，把A列数据中间两位数字取出，换算成月份，分别保存在另一列。这个操作明显违背了高效数据。那么，在不使用辅助列的情况下，能否用一个综合公式来解决问题呢？答案是肯定的，即使用SUMPRODUCT函数。

2.SUMPRODUCT函数的基本原理。

SUMPRODUCT的功能是将多个数组的对应元素相乘，然后将这些乘积相加。语法如下：

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

顾名思义，从函数名来看，sumproducer=sumproduct。

使用此功能时，请记住以下两点：

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

非数字的数组元素被视为0。例如，逻辑值“真”和“假”被视为0。要将真还原为数字1，将假还原为数字0，您可以将它们乘以1:真*1，假* 1)。

我们将用两个例子来解释SUMPRODUCT函数的基本原理和用法。

例1

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

对于这样的问题，很多人会采用这样的做法：在数据区的右侧插入两个辅助列，分别计算每个产品的销售金额和折扣金额，然后用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

下图是五个评价指标的评分表，每个指标的权重不同。现在，我们需要计算每个人的分数，而这些分数就是每个指标分数的总数乘以指标的权重，也就是数学上的。那么，计算公式如下。

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

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

但是，条件表达式的结果是两个逻辑值，真和假，SUMPRODUCT会将这两个逻辑值都视为0。因此，有必要将条件表达式乘以数字1，或除以数字1，或输入两个负号将其转换为数字1和0。