SUMPRODUCT函数基本原理详解

SUMPRODUCT函数返回相应的数组或区域乘积的和。其语法为：

SUMPRODUCT(数组1, [数组2], [数组3], …)

其中：

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)

SUMPRODUCT函数是怎么做的呢？看下：

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

以上是SUMPRODUCT函数最基础的用法。在Excel中，创造性地使用函数，往往能够收到令人意想不到的效果。

应用技巧

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

由于SUMPRODUCT函数总是将其参数数组相对应的元素相乘后求和，因此我们可以使用一个数组，其中包含的元素由TRUE/FALSE或1/0组成，TRUE/1是对应着想要求和的元素，而FALSE/0对应着不想求和的元素。

如下所示，仅仅想求出北区的商品销售总额。

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

但是，当我们使用公式：

=SUMPRODUCT(D4:D8,E4:E8,{FALSE;TRUE;FALSE;FALSE;TRUE})

会得到结果：-

因为此时Excel并不会将TRUE/FALSE值自动转换成1/0。那么，我们需要强制将TRUE/FALSE转换：

=SUMPRODUCT(D4:D8,E4:E8,1*{FALSE;TRUE;FALSE;FALSE;TRUE})

我们使用1与TRUE/FALSE值的数组相乘，使其转换成数组：{0;1;0;0;1}。这样，相乘的三个数组如下所示：

然而，我们不可能每次在公式中添加一个确定值的数组，因为如果要求和的条件发生改变的话，我们必须将这个数组进行相应的更改。因此，我们对公式进行改进：

=SUMPRODUCT(D4:D8,E4:E8,1*(C4:C8=”北区”))

此公式中的：1*(C4:C8=”北区”)与1*{FALSE;TRUE;FALSE;FALSE;TRUE}等价。

实际上，如果我们将要查找的值放置在某个单元格（例如G3）中，那么可以将公式修改为：

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

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

如果不想额外的乘以1，可以使用公式：

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

再举一个示例。如下所示，想要求出洗手液在西区超市的销售量之和。

可使用公式：

=SUMPRODUCT(–(A2:A21=”洗手液”),–(B2:B21=”西区”),C2:C21)

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

–(A2:A21=”洗手液”) =-1×-1×(A2:A21=”洗手液”)=1×(A2:A21=”洗手液”)

其作用是强制将包含TRUE/FALSE值的数组转换成包含1/0的数组。