函数功能

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

函数语法

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

参数解释

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

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

实例1 统计总销售金额

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

选中F1单元格,在公式编辑栏中输入公式:

=SUMPRODUCT(B2:B5,C2:C5)

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

excel sum函数用法(Excel数学函数SUMPRODUCT函数详解)(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所示。

excel sum函数用法(Excel数学函数SUMPRODUCT函数详解)(2)

图2

公式解析

excel sum函数用法(Excel数学函数SUMPRODUCT函数详解)(3)

①在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所示。

excel sum函数用法(Excel数学函数SUMPRODUCT函数详解)(4)

图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所示。

excel sum函数用法(Excel数学函数SUMPRODUCT函数详解)(5)

图4

公式解析

excel sum函数用法(Excel数学函数SUMPRODUCT函数详解)(6)

①依次判断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所示。

excel sum函数用法(Excel数学函数SUMPRODUCT函数详解)(7)

图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所示。

excel sum函数用法(Excel数学函数SUMPRODUCT函数详解)(8)

图6

公式解析

excel sum函数用法(Excel数学函数SUMPRODUCT函数详解)(9)

①依次判断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所示。

excel sum函数用法(Excel数学函数SUMPRODUCT函数详解)(10)

图7

公式解析

excel sum函数用法(Excel数学函数SUMPRODUCT函数详解)(11)

①用ROW函数(它用于返回引用的行号)提取A2~A11单元格的各个行号。

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

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

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

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

选中C15单元格,在公式编辑栏中输入公式:

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

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

excel sum函数用法(Excel数学函数SUMPRODUCT函数详解)(12)

图8

公式解析

excel sum函数用法(Excel数学函数SUMPRODUCT函数详解)(13)

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

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

,