Excel数学函数——SUMPRODUCT函数详解

函数功能

SUMPRODUCT函数是指在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。

函数语法

SUMPRODUCT(array1, [array2], [array3], ...)

参数解释

array1：必需。其相应元素需要进行相乘并求和的第一个数组参数。

array2, array3...：可选。为2～255个数组参数，其相应元素需要进行相乘并求和。

实例1　统计总销售金额

当统计了各类产品的销售数量和销售单价后，可以使用SUMPRODUCT函数来计算产品的总销售额。

选中F1单元格，在公式编辑栏中输入公式：

=SUMPRODUCT(B2:B5,C2:C5)

按“Enter”键即可计算出产品总销售额，如图1所示。

图1

公式解析

=SUMPRODUCT(B2:B5,C2:C5)

分别将B2:B5与 C2:C5单元格区域中的值进行一一对应乘法运算，并返回其乘积之和。

实例2　同时统计某两种型号产品的销售件数

在产品销售报表中，若要统计指定型号的产品销售件数（本例将统计“YL_a”和“YL_b”产品型号的销售件数），可以使用SUMPRODUCT函数来实现。

选中E1单元格，在公式编辑栏中输入公式：

=SUMPRODUCT((($A$2:$A$7="YL_a")+($A$2:$A$7="YL_b")),$B$2:$B$7)

按“Enter”键即可计算出产品型号为“YL_a”和“YL_b”的销售件数，如图2所示。

图2

公式解析

①在A2:A7单元格区域中依次判断各个单元格的值是否为“YL_a”或者“YL_b”，若为两个中的任意一个则返回TRUE，否则为FALSE。然后将TRUE转换为1，FALSE转换为0，返回的是一个数组。

②将步骤①数组中值为1的行对应在B2:B7单元格区域中的销售件数进行求和运算。

实例3　计算商品打折后的总金额

根据商品的单价、数量以及折扣信息，可以利用公式计算出打折后的商品总金额。

选中C11单元格，在公式编辑栏中输入公式：

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

按“Enter”键即可计算出所有商品折扣后的价格，如图3所示。

图3

公式解析

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

依次将B2:B9、C2:C9、D2:D9单元格区域上的值一一对应相乘，将相乘的结果求和。

实例4　统计指定部门获取奖金的人数（去除空值）

表格统计了各个部门员工的奖金发放记录，没有奖金的用空值显示，使用SUMPRODUCT函数可以统计出指定部门获取奖金的人数。

➊ 选中F5单元格，在公式编辑栏中输入公式：

=SUMPRODUCT(($B$2:$B$12=E5)*(C$2:C$12<>""))

按“Enter”键即可统计出所属部门为“业务部”获取奖金的人数。

➋ 将光标移到F5单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可快速统计出其他指定部门获取奖金的人数，如图4所示。

图4

公式解析

①依次判断B2:B12单元格区域的值是否等于E5单元格的值，如果是则返回TRUE，否则返回FALSE，返回的是一个数组。

②依次判断C2:C12单元格区域的值是否不为空，如果是则返回TRUE，否则返回FALSE，返回的是一个数组。

③ 当步骤①与②同时为TRUE时，返回1，否则返回0。返回的也是一个数组，然后使用SUMPRODUCT函数对数组进行求和，即1出现的个数。

实例5　计算指定部门、指定职位的员工人数值

若要统计出指定部门、指定职位的员工总人数，可以使用SUMPRODUCT函数来实现。

➊ 选中F5单元格，在公式编辑栏中输入公式：

=SUMPRODUCT(($B$2:$B$9=E5)*($C$2:$C$9="职员"))

按“Enter”键即可统计出所属部门为“财务部”的员工人数。

➋ 将光标移到F5单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可快速统计出其他指定部门的员工人数，如图5所示。

图5

公式解析

①依次判断B2:B9单元格区域的值是否等于E5单元格的值，如果是则返回TRUE，否则返回FALSE，返回的是一个数组。

②依次判断C2:C9单元格区域的值是否为“职员”，如果是则返回TRUE，否则返回FALSE，返回的是一个数组。

③ 当步骤①与②同时为TRUE时，返回1，否则返回0。返回的也是一个数组，然后使用SUMPRODUCT函数对数组进行求和，即1出现的个数。

实例6　计算指定店面指定类别产品的销售金额合计值

表格中分店面、品牌、品类统计了产品的销量，通过设计公式可以计算出指定店面、指定品类产品的总销售量。例如，计算出店面“2”与品类“1”产品的销量合计值。

选中C12单元格，在公式编辑栏中输入公式：

=SUMPRODUCT((A2:A11=2)*(C2:C11=1)*D2:D11)

按“Enter”键即可统计出2店面中1品类产品的销量合计值，如图6所示。

图6

公式解析

①依次判断A2:A11单元格区域的值是否等于“2”，如果是则返回TRUE，否则返回FALSE，返回的是一个数组。

②依次判断C2:C11单元格区域的值是否为“1”，如果是则返回TRUE，否则返回FALSE，返回的是一个数组。

③ 当步骤①与②同时为TRUE时，返回1，否则返回0，返回的也是一个数组。然后将数组中为1的行对应D2:D11单元格区域上的值返回，

最后使用SUMPRODUCT函数对返回的值求和。

实例7　分奇偶行统计数据

表格统计了学生考试成绩，其中学生分属于1班和2班，现在要求统计出1班的总成绩。本例中1班都显示在偶数行，2班都显示在奇数行，分析这一特点可以便于对公式进行设计。

选中E2单元格，在公式编辑栏中输入公式：

=SUMPRODUCT(C2:C11,MOD(ROW(A2:A11)+1,2))

按“Enter”键得出1班的总分，如图7所示。

图7

公式解析

①用ROW函数（它用于返回引用的行号）提取A2～A11单元格的各个行号。

②用MOD函数（它用于求两个数值相除后的余数，其结果的正负号与除数相同）判断步骤①返回的行号加1后是否能被2整除。

③ 将不能整除的对应在C列的数据进行求和得出1班总分（2班是能整除的对应在C列的数据和）。

实例8　统计非工作日的销售金额

表格给出了日期及其对应的星期，可以使用SUMPRODUCT函数统计周六、周日的销售额合计值。

选中C15单元格，在公式编辑栏中输入公式：

=SUMPRODUCT((MOD(A2:A13,7)<2)*C2:C13)

按“Enter”键即可统计出非工作日（即周六、日）的销售金额之和，如图8所示。

图8

公式解析

①用MOD函数（它用于求两个数值相除后的余数，其结果的正负号与除数相同）判断A2:A13单元格区域中各单元格的日期序列号与7相除后的余数是否小于2（因为星期六的日期序列号与7相除的余数为0，星期日的日期序列号与7相除的余数为1）。

②如果步骤①结果为TRUE，则将对应在C2:C13单元格区域中的值求和。

以上内容由优质教程资源合作伙伴 “鲸鱼办公” 整理编辑，如果对您有帮助欢迎转发分享！

你可能对这些文章感兴趣：

• Excel表格十大函数(Excel表格函数公式出现错误怎么办?)
• iphone应用图标大小要怎么调整？(iPhone应用图标大小)
• 有办法了！批量删除多个Word页眉页脚
• RANKIF？Excel中有这个函数吗？
• 手机与和U盘的连接及文件操作(手机直接连接u盘为什么不能互传文件)
• 人像摆拍姿势和抓拍技巧，学会“安排”照片，摄影新手也能拍美照
• 调整干部开启，如何找准自己的位置？以下几点值得借鉴
• Excel表格办公—-公式与结果共同显示的方法
• 人人都该掌握的电脑小技巧，建议收藏
• 8个赞不绝口的电脑PC软件，款款都是黑科技，请低调使用