今天，我将和大家一起分享多条件求和函数SUMPRODUCT的用法，用好该函数，你可以轻松计算出多组乘积的和。
一、SUMPRODUCT函数基础知识
SUMPRODUCT函数定义：SUMPRODUCT函数是在给定的几组数组中，将数组间对应的元素相乘，并将多组乘积进行求和。相当于将几个数组直接相乘，再使用SUM函数进行求和。数组参数必须具有相同的维数（即大小相同，如A3:B8,C3:D8），否则，函数SUMPRODUCT将返回错误值#VALUE!。SUMPRODUCT函数将非数值型的数组元素作为0处理。
语法：SUMPRODUCT(数组1,数组2,……数组N)
数组1：必需参数，是指相应元素需要进行相乘并求和的第一个数组参数。
数组2……数组N：可选参数，2到255个数组参数，其相应元素需要进行相乘并求和。
例如：要计算A、B列和C、D列的乘积之和，在E11单元格输入公式：=SUMPRODUCT(A3:B8,C3:D8)，即可得到对应乘积之和，对应的是A列乘C列，B列乘D列，再把所有乘积相加。见下图：
上图中，两个数组的所有元素对应相乘，然后把乘积相加，即9*2 9*7 5*5 8*10 10*2 6*8……4*8 9*2=395。SUMPRODUCT函数进行的运算是数组运算，因此在输入公式的时候可以直接以普通公式的形式输入，它的运算结果完全等同于数组公式。而使用直接相乘再使用SUM函数求和的方法，需要以数组公式的形式输入。
本例所返回的乘积之和，与以数组形式输入的公式=SUM(A3:B8*C3:D8)的计算结果相同。
二、SUMPRODUCT函数案例实践
清楚了SUMPRODUCT函数的定义、语法及相关知识，下面，我们进行几个案例解析。
（一）统计年龄在50岁及以上的职工人数
某部门的“人员信息表”中有姓名、年龄、性别等基本信息，现在需要计算年龄在50岁及以上的职工人数。
本例中，需要计算年龄在50岁及以上的职工人数，这是一个单条件计数问题，一般用COUNTIF函数处理，此例我们选择SUMPRODUCT函数。
在E11单元格输入公式：=SUMPRODUCT((C3:C8>=50)*1)，按回车键即可计算出年龄在50岁及以上的职工人数。见下图：
刚才讲过，如果SUMPRODUCT函数的参数中有非数值型数据，函数在计算时会自动将其当做0处理，逻辑值TRUE和FALSE也不例外。因此，在本例中，需要在表示条件的表达式C3:C8>=50计算出来的是文本数字，需要在后面加上“*1”将其转换为数值。“*1”的作用就是将文本数字转换为数值型数字。
使用SUMPRODUCT函数可以解决大部分单条件计数的问题，在计数时，其功能和COUNTIF函数几乎没有区别，本例中若使用COUNTIF函数，公式可写成：=COUNTIF(C3:C8,">=50")。
（二）统计年龄在50岁及以上的女职工人数
跟上一个例子类似，本例中，需要计算年龄在50岁及以上的女职工人数，这是一个多条件计数问题，一般用COUNTIFS函数处理，此例我们选择SUMPRODUCT函数。
在E11单元格输入公式：=SUMPRODUCT((B3:B8="女")*1,(C3:C8>=50)*1)，按回车键即可计算出年龄在50岁及以上的女职工人数。见下图：
本例中的公式可以简化，不使用“*1”转换数据类型，简化后的公式为：=SUMPRODUCT((B3:B8="女")*(C3:C8>=50))。为什么不使用“*1”呢？因为两个逻辑值相乘的结果为数值。
虽然SUMPRODUCT函数在解决大部分多条件计数问题时与COUNTIFS函数的功能相同，那么本例用COUNTIFS函数来实现的话，公式可以写为：=COUNTIFS(B3:B8,"女",C3:C8,">=50")。
（三）计算男职工交纳工会费的总和
某部门要计算男职工的工会费总和，这是一个单条件求和问题，一般用SUMIF函数来处理，本例用SUMPRODUCT函数进行讲解。
在E11单元格输入公式：=SUMPRODUCT((B3:B8="男")*E3:E8)，按回车键，即可计算出男职工交纳工会费的总和。见下图：
使用SUMPRODUCT函数可以解决大部分单条件求和问题，求和时的功能和SUMIF几乎没有区别。本例如果使用SUMIF函数求和，其公式可写成：=SUMIF(B3:B8,"男",E3:E8)。
本例同样可以使用SUM函数的数组公式计算，其公式可写成：=SUM((B3:B8="男")*E3:E8)，按【Ctrl Shift Enter】三键组合。
（四）计算科室二女职工工会费总额
某部门要计算科室二女职工的工会费总和，这是一个多条件求和问题，一般用SUMIFS函数来处理，本例用SUMPRODUCT函数进行讲解。
在E11单元格输入公式：=SUMPRODUCT((D3:D8="科室二")*E3:E8*(B3:B8="女"))，按回车键，即可计算出男职工交纳工会费的总和。见下图：
使用SUMPRODUCT函数可以解决大部分多条件求和问题，求和时的功能和SUMIFS几乎没有区别。本例如果使用SUMIFS函数求和，其公式可写成：=SUMIFS(E3:E8,D3:D8,"科室二",B3:B8,"女")。
使用SUMPRODUCT函数对参数的位置没有任何要求，SUMIFS函数对参数的位置要求十分严格。几乎没有区别。本例SUMPRODUCT函数公式可写成：=SUMPRODUCT((D3:D8="科室二")*(B3:B8="女")*E3:E8)，参数的位置可以随意摆放。
本例同样可以使用SUM函数的数组公式进行计算，其公式可写成：=SUM((D3:D8="科室二")*(B3:B8="女")*E3:E8)，按【Ctrl Shift Enter】三键组合。
以上就是SUMPRODUCT函数的几个经典案例，想要学习好EXCEL函数，要学会多个函数的组合，后续课程中我们会讲到多函数结合的用法，能进行更为复杂的计算，对提升工作效率有很大帮助。
评论 (0)