没有SUMPRODUCT怎么学Excel函数？

大家好，今天给大家分享一个很常见也很实用的功能：SUMPRODUCT。众所周知，条件求和和计数是表用户最常遇到的两个问题，而这个函数同时具有条件求和和计数的功能。所以我必须学习。

基本语法

SUMPRODUCT的官方语法描述是将给定数组组中的对应元素相乘，并返回乘积之和。语法格式如下：

=SUMPRODUCT(array1，array2，array3，…)

——SUM表示求和，乘积表示乘法。参数相乘，然后求和。你看，SUMPRODUCT真的和它的名字一样有名。

看我的手，歪、图、慢……综上所述，SUMPRODUCT功能有以下三个特点：

默认情况下，1本身执行数组操作。

2将参数中的非数字数组元素视为0。

3参数必须具有相同的大小，否则将返回错误值。

特征分析

看了SUMPRODUCT的简历，觉得很多朋友只是在雾里看花，对它只有模糊的认识。这些特征意味着什么？它能做什么样的工作？其实也不清楚。

让我举几个例子。

在上图所示的数据表中，C列为商品单价，D列为销售数量。现在需要计算单元格C9中的总销售额。

C9输入以下公式，得到11620.60的结果。

=SUMPRODUCT(C3:C7，D3:D7)

这是一个简单的SUMPRODUCT函数。其运算过程是：C3:C7和D3:D7分别将两个面阵中的元素相乘，即C3 * D3、C4 * D4、C5 * D5 …直到C7*D7。

等于先计算每种商品的销售金额，最后汇总求和。

SUMPRODUCT函数的第一个特点是支持数组间运算，所以这个公式虽然执行了很多运算，但并不需要按数组三键就可以结束公式输入。

有朋友说，公式也可以这样写：

=SUMPRODUCT(c 3: C7 * d 3:d 7)

或者使用下面的数组公式。

=SUM(C3:C7*D3:D7)

那么这三个公式有什么区别呢？

首先，在大多数情况下，SUMPRODUCT函数可以在不输入数组三键结束公式的情况下进行数组运算，而SUM函数是必需的。

其次，我们将讨论SUMPRODUCT函数的另一个非常重要的特性。

……

我们对上表稍作改动，将“笔”的销售数量改为：尚未统计。还需要计算C9单元的销售总额。

此时，如果使用公式：

=SUMPRODUCT(c 3: C7 * d 3:d 7)

或数组公式：

=SUM(C3:C7*D3:D7)

错误值#VALUE！

返回错误值的原因是D4单元格“尚未计数”是文本值，文本值不能直接参与数学运算，所以C4*D4返回错误值#VALUE！这又会导致整个公式的结果返回一个错误值。

但是，使用下面的公式在这方面不会有麻烦，并且会直接返回正确的结果：

=SUMPRODUCT(C3:C7，D3:D7)

这是SUMPRODUCT函数的第二个特性：它将非数字数组元素视为0。

本例中，D4单元格“尚未计数”的值是文本，不是数值，SUMPRODUCT主动将其视为零，所以C4*D4，结果也是零，其他数组元素照常计算，结果为11385.60。

特别是，SUMPRODUCT将非数字数组元素视为0。所谓的非数字数组元素包含逻辑值和文本，但不包含错误值。如果数组元素包含错误值，公式也会返回错误值，例如本例中的第一个公式。

……

在谈完SUMPRODUCT函数的两个特性之后，我们再来谈谈它的第三个特性：数组参数必须有相同的大小，否则会返回一个错误值。

我们还是以上图的例子为例，继续计算商品的销售总额。如果我们在C9中输入公式：

=SUMPRODUCT(C3:C7，D3:D6)

会有什么结果？

错误值：#VALUE！

为什么呢？

小心，你一定注意到了，两个面积数组，C3:C7，显然比D3:D6多了一个元素。C3和D3配对，C4和D4配对……那么C7和谁配对呢？女人结婚了，结果只剩下一个单身汉。这一天不能过去！萝卜有坑，但只有萝卜没有坑。这不是杀萝卜吗？

3354所以SUMPRODUCT不高兴，它给你一个错误值#VALUE！明确告诉你，和谐时代的幸福岁月，日子不可能如此。

这是SUMPRODUCT函数的第三个特性：数组参数必须具有相同的大小，否则将返回一个错误值。

下面是一个练习，你看，你能用SUMPRODUCT函数做吗？

案例开发

我们假设下图是某公司工资发放的部分记录(数据纯粹是虚拟的，如果有相似之处，就是交叉)。a栏为工资发放时间，b栏为员工所属部门，c栏为员工姓名，d栏为相关员工领取的工资金额。

3354然后，问题和广告来了：

一个

员工在西门庆领过多少次工资？

这是一个单条件计数问题。通常我们使用COUNTIF函数，但是如果我们使用SUMPRODUCT函数，一般会写成如下：

=SUMPRODUCT((C2:C13=“西门青”)*1)。

首先判断C2:C13的值是否等于“西门清”，等于则返回TRUE，不等于则返回FALSE，从而建立具有逻辑值的存储阵列。

如上所述，SUMPRODUCT有一个特性，将非数值数组元素视为0，逻辑值自然属于非数值数组元素。为了避免SUMPRODUCT函数将逻辑值视为0而导致的统计错误，我们使用*1将逻辑值转换为数值，TRUE转换为1，FALSE转换为0，最后进行统计求和。

2

西门庆员工拿多少工资？

这是一个单条件求和问题，通常我们用SUMIF函数，如果我们用SUMPRODUCT函数，我们可以把它写成如下：

=SUMPRODUCT((C2:C13=“西门青”)*D2:D13)。

首先判断C2:C13的值是否等于“西门清”，得到逻辑值FALSE或TRUE，再乘以D2:D13的值。真乘以值得到值本身。True乘以一个数字返回0。最后，通过统计求和得到结果。

看完以上两个问题，可能有些朋友心里会想，如果SUMIF和COUNTIF能做一些看起来有能力做SUMPRODUCT的事情，并且能做得更好，那么SUMPRODUCT为什么要做呢？

伙计们，我不能这么说。SUMPRODUCT是上厅下厅的厨房，对工作环境不挑三拣四。它对参数类型没有任何特殊要求。COUNTIF和SUMIF是不同的。它们需要单独的参数，这些参数必须是范围类型，并且不支持数组。例如，对于以下两个问题，COUNTIF和SUMIF将被绕过。

三

外交部2月份发了多少次工资？总数是多少？

问题一：外交部2月份发了多少次工资？

这是一个多条件计数问题。

第一个条件，发工资的时间必须是2月；第二，发工资的部门必须是外交部。

如果用多条件计数函数COUNTIFS来判断发工资的时间是否属于6月份，问题会比较复杂。利用SUMPRODUCT函数，我们可以写出如下公式：

=sumproduct((月(a2:a13)=2) * (b23360b13="外交部")。

问题二：外交部2月份发了多少工资？

这是一个常见的多条件求和问题。

如果用SUMIFS函数来判断发工资的时间是否属于6月份，那么简单的问题就复杂了。

SUM向你走来：

=sumproduct((月(a2:a13)=6) * (b2:b13=“财务部”)，D2:D13。

或者：

=sumproduct((月(a2:a13)=6) * (b2:b13=“财务部”)*D2:D13。

拍拍手，关于这两种形式SUMPRODUCT函数的区别，我们上面已经详细讲解过了——，你还记得吗？

上述公式可以说是SUMPRODUCT多条件求和的典型用法，可以概括为：

=SUMPRODUCT((条件1) *(条件2)……，求和区域)。

四

外交部和步兵部2月份发了多少工资？

解决了以上问题，相信大家都已经知道外交部2月份发的工资怎么算了，那么外交部和步兵部2月份发了多少工资呢？

我们经常看到一些简单的表亲这样写公式：

如果代码不完整，可以左右拖动..

=sumproduct((月(a2:a13)=2) * (b2:b13=“外交部”)*D2:D13) sumproduct((月(a2:a13)=2) * (b23360b13=“步兵部。

这些表兄弟大概想，不就是算计两个部门吗？不要说二，我可以数到二十，一加一，一直加到二十。世界上没有什么是困难的，但是如果你用心去做，我可以一砖一瓦地建造长城.

公式写了这么长，先不谈计算速度。首先，很累。如果写错了，需要重新修改。也很麻烦。哭闹——真讨厌。

事实上，我们可以这样写：

=sumproduct((月(a2:a13)=6) * (b2:b13={“财务部”、“市场部”})*D2:D13)。

五

排名应用

了解SUMPRODUCT函数在条件计数和求和中的用法，最后我们来看一个SUMPRODUCT函数在排序中的用法。

如上图所示，某个公司的一些人在某个月收到了一些工资，然后他们想看看自己的工资和在部门中的排名。比如步兵部的陆就是一个老员工，很想知道他们的工资在各自部门的排名数。

当然，不知道的话，一排就傻了。

SUMPRODUCT通过将D2输入公式复制下来的方式解决了这个问题：

=SUMPRODUCT(($ 2:澳元$9澳元=A2)*(C2 $ 2:加元$9加元))1

(想想公式最后1为什么不直接写成如下：)。

=SUMPRODUCT(($ A $ 2: $ A $ 9=A2)*(C2=$ C $ 2: $ C $ 9))

结束语

唠叨了这么多，眼睛又酸又麻。该结束了。

最后，请思考两个小问题：

问题1:下面的SUMPRODUCT函数有多少个参数？

=sumproduct((月(a2:a13)=6) * (b2:b13=“财务部”)*D2:D13。

以下SUMPRODUCT函数有几个参数。

=sumproduct((月(a2:a13)=6) * (b2:b13=“财务部”)，D2:D13。

第二个问题：

为什么SUMPRODUCT有时比SUMIF/COUNTIF慢？