随着SUMIFS、COUNTIFS等一些函数的出现，函数SUMPRODUCT渐渐被遗忘，由于前者的运行速度比他快而被慢慢淡出了excel接触者的视线。然并卵，我想说函数SUMPRODUCT的强大功能足以让函数SUMIFS、COUNTIFS等失业，而数组公式函数SUMPRODUCT能取代函数SUM并且不需要按<Ctrl Shift Enter>三键结束。
一、函数语法解析
1、函数定义：
在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。
2、语法格式：
SUMPRODUCT(array1,[array2],[array3],...)
SUMPRODUCT(数组区域1，数组区域2，数组区域3，……)
3、参数说明：
array1：必需。其相应元素需要进行相乘并求和的第一个数组参数。
array2，array3，...：可选。2到255个数组参数，其相应元素需要进行相乘并求和。
二、扩展语法
1、多条件计数语法：
SUMPRODUCT((条件1区域='条件1')*(条件2区域='条件2')*(……))
2、多条件求和语法：
①、SUMPRODUCT((条件1区域='条件1')*(条件2区域='条件2')*(……)*求和区域)
②、SUMPRODUCT((条件1区域='条件1')*(条件2区域='条件2')*(……),求和区域)
3、求和语法中求和区域前使用“,”与“*”的区别：
多条件求和时，当求和区域中没有文本时“,”与“*”可以通用；
当求和区域中有文本时使用“*”会返回错误值#VALUE!，故而只能使用“,”。
三、函数示例
1、基础应用
▲
01
文本数字求和
输入公式：=SUMPRODUCT(--(B3:B9))
解析：该公式中两个负号，一个负号是把文本型数字转换成负数值,另一个负号是把负数值转换成需要的正数。
当然转换的方法有很多种，可以用“ 0”、“-0”、“*1”、“/1”、“^1”等等，关键看你自己怎么用。
有人觉得用函数SUMPRODUCT字符太长了，用函数SUM明显字符少很多，该题中用函数SUM代替函数SUMPRODUCT，那就必须按Ctrl Shift Enter三键结束。
▲
02
忽略文本求和
输入公式：=SUMPRODUCT(A15:A22)
▲
03
有文本的数组相乘
输入公式：
=SUMPRODUCT(C29:C34,D29:D34)
解析：函数SUMPRODUCT将非数值型的数组元素作为0处理。
如果该题中用单一的函数SUM来解决，按三键结束是小事，会出现错误值#VALUE!。
函数SUM的参数为不能转换成数字的文本,将会显示错误。
函数SUM的解决方法：=SUM(IFERROR(C29:C34*D29:D34,''))，按三键结束。
▲
04
单条件计数数
输入公式：=SUMPRODUCT(--(B41:D47>=80))
解析：(B41:D47>=80)部分判断条件是否成立，成立返回TRUE，不成立返回FALSE，
发生运算时TRUE相当于1，FALSE相当于0，
然后用函数SUMPRODUCT或函数SUM求和，
注意使用函数SUM时一定要按三键结束。
同样的可以用“ 0”、“-0”、“*1”、“/1”、“^1”等等来转换。
▲
05
单条件求和
输入公式：
=SUMPRODUCT((B54:D60>=80)*B54:D60)
解析：(B54:D60>=80)部分判断条件是否成立，成立返回TRUE，不成立返回FALSE，
再*B54:D60得到的就是满足条件的，
最后用函数SUMPRODUCT或函数SUM求和，
注意使用函数SUM时一定要按三键结束。
得到逻辑值TRUE和FALSE后直接*B54:D60就已发生运算，如果像上题一样用“ 0”、“-0”、“*1”、“/1”、“^1”等等来转换虽不影响结果，但明显是多此一举。
2、进阶应用
▲
01
单条件数组相乘
输入公式：=SUMPRODUCT((A3:A8='烧烤')*C3:C8*D3:D8)
或者：=SUMPRODUCT((A3:A8='烧烤')*C3:C8,D3:D8)
▲
02
多条件计数
输入公式：=SUMPRODUCT((B15:B21='女')*(C15:C21>=60))
▲
03
多条件求和
输入公式：=SUMPRODUCT((B28:B34='女')*(C28:C34>=60)*C28:C34)
或者：=SUMPRODUCT((B28:B34='女')*(C28:C34>=60),C28:C34)
▲
04
有条件的区间计数
输入公式：=SUMPRODUCT((B41:B47='女')*(C41:C47>=60)*(C41:C47<=80))
▲
05
有条件的区间求和
输入公式：=SUMPRODUCT((B54:B60='女')*(C54:C60>=60)*(C54:C60<=80)*C54:C60)
或者：=SUMPRODUCT((B54:B60='女')*(C54:C60>=60)*(C54:C60<=80),C54:C60)
上述示例可以参照前面说的扩展语法格式来，也可以用函数SUMIFS、COUNTIFS来完成，关于这两个函数的用法不再赘述，也可以用函数SUM代替函数SUMPRODUCT，需要注意的是要按三键结束。
3、组合应用
▲
01
隔列求和
输入公式：
=SUMPRODUCT((MOD(COLUMN(B:I),2)=0)*B3:I3)
▲
02
零钞的计算
注：第一行中的“元”是通过设置单元格格式自定义加上去的，若是直接输入的“元”就要先提取数字或把“元”替换掉。
在C15单元格输入公式：
=INT(($B15-SUMPRODUCT($A$14:B$14,$A15:B15) 1%%)/C$14)，向右向下填充。
思路：当前面额张数=(工资-(已计算面额*已计算面额张数))/当前面额
解析：SUMPRODUCT($A$14:B$14,$A15:B15)部分算出了左侧单元格中已经计算张数的金额，用B列的工资减去这个金额，然后除以当前的面额，再用INT函数取整就可以得到对应面额的钞票数量。
留个问题：为什么要加1%%呢？加1%、1%%%等可不可以呢？不加又可不可以呢？
▲
03
多条件求不重复的个数
输入公式：
=SUMPRODUCT(1/COUNTIFS(A30:A38,A30:A38,B30:B38,B30:B38))
▲
04
中式排名
输入公式：
=SUMPRODUCT((C$45:C$50>C45)*(1/(COUNTIF(C$45:C$50,C$45:C$50)))) 1
▲
05
包含求和
①、求姓名中包含6，性别为男的销售额之和
输入公式：
=SUMPRODUCT(ISNUMBER(FIND('6',A57:A62))*(B57:B62='男')*C57:C62)
或者：=SUMPRODUCT(ISNUMBER(FIND('6',A57:A62))*(B57:B62='男'),C57:C62)
解析：函数SUMPRODUCT不能使用通配符，故而与函数FIND组合实现包含求和功能。
函数SUMIFS通配符解法：=SUMIFS(C57:C62,A57:A62,'*6*',B57:B62,'男')
②、求姓名中有波形符~的销售额之和
输入公式：
=SUMPRODUCT(ISNUMBER(FIND('~',A57:A62))*C57:C62)
解析：函数SUMPRODUCT不能使用通配符，故而与函数FIND组合实现包含求和功能。
函数SUMIF通配符解法：
=SUMIF(A57:A62,'*~~*',C57:C62)
四、注意事项
①、数组参数必须具有相同的维数。否则，函数SUMPRODUCT将返回#VALUE!错误值。
②、函数SUMPRODUCT将非数值型的数组元素作为0处理。
③、函数SUMPRODUCT不能使用通配符，但可以与函数FIND组合实现包含功能。
④、函数SUMPRODUCT是不用按<Ctrl Shift Enter>三键结束的数组运算函数。
作者：仰望星空
联系客服
