SUMPRODUCT功能基本原理详解

sum函数返回相应数组或区域的SUM乘积之和。它的语法是：

SUMPRODUCT (array1，[array2]，[array3]，…)。

其中：

1.数组1，必选，指定要相乘和相加的数组数据。

2.数组2，数组3，…，可选，指定要相乘和相加的数组数据。数组的数量不超过255。

3.指定的数组可以是数组、单元格区域或代表单元格区域的名称或代表公式的名称，例如{ 1；2;3}、A1:A10或MyRange。

例如，如下所示：

公式1:

=SUMPRODUCT({ 1；2;3})

得到结果6，即对数组元素求和：

=1 2 3

等式2:

=SUMPRODUCT(C4:C6)

对指定的单元格区域求和，得到结果6。

等式3:

=SUMPRODUCT(MyRange1)

命名区域相加，名称MyRange1代表的区域为C4:C6，结果为6。

等式4:

=SUMPRODUCT(MyRange2)

将命名公式相加，名称MyRange2引用的公式为：=OFFSET(Sheet1！$C$3，1，0，3，1)，即单元格区域C4:C6，结果为6。

顾名思义，SUMPRODUCT函数由SUM PRODUCT组成，即积和和。例如，下面显示的示例。

如果需要所有商品的销售总额，可以通过数量乘以单价得到每个商品的销售金额，然后将结果相加在一起。但是，使用SUMPRODUCT函数可以直接找到结果：

=SUMPRODUCT(D4:D8，E4:E8)

SUM函数是做什么的？看看这个：

可以清楚地看到，SUMPRODUCT函数首先将数组中对应的元素相乘，然后将这些乘积相加，得到最终的结果。

以上是SUMPRODUCT函数最基本的用法。在Excel中，创造性地使用函数往往可以得到意想不到的效果。

应用技能

接下来，我们使用SUMPRODUCT函数实现条件求和。

由于SUMPRODUCT函数总是将与其参数数组对应的元素相乘并求和，所以我们可以使用一个数组，其中元素由TRUE/FALSE或1/0组成，其中TRUE/1是需要求和的元素，FALSE/0是不需要求和的元素。

如下图，我们只是想了解一下北区的商品销售总额。

此时，我们可以添加一个包含逻辑值的数组来过滤掉不需要求和的数据。原理如下。

然而，当我们使用公式时：

=SUMPRODUCT(D4:D8，E4:E8，{ FALSE真；假；假；真})

将得到结果：-

Excel此时不会自动将真/假值转换为1/0。然后，我们需要强制进行真/假转换：

=SUMPRODUCT(D4:D8，E4:E8，* { FALSE真；假；假；真})

我们将1乘以一个真/假值数组，将其转换为一个数组：{ 0；1;0;0;1}。因此，三个数组相乘如下：

但是，我们不可能每次都在公式中加入一个确定值的数组，因为如果需要的条件发生变化，我们必须相应地改变数组。因此，我们改进了公式：

=sumproduct (d4:d8，e4:e8，1 * (c4:c8=“北区”))。

在这个公式中：1*(C4:C8=“北区”)和1 * { FALSE真；假；假；真}等效。

实际上，如果我们将我们要寻找的值放在一个单元格中(如G3)，我们可以将公式修改为：

=SUMPRODUCT(D4:D8，E4:E8，1*(C4:C8=G3))

这样就可以直接修改这个单元格中的值，得到相应的结果。

如果不想乘以1，可以使用以下公式：

=SUMPRODUCT(D4:D8)，(E4:E8)*(C4:C8=G3))

再举一个例子。如下图，我们想找到西超市洗手液销量的总和。

可以使用以下公式：

=sum product((a 2: a 21="洗手液")、(b 23360 b 21="西区")、C2:C21。

在公式中，使用了双减号：

(a 2: a 21=“洗手液”)=-1-1(a 23360 a 21=“洗手液”)=1(A2:A21=“洗手液”)。

它的功能是强制将包含真/假值的数组转换为包含1/0的数组。