SUMPRODUCT函数的经典用法

SUMPRODUCT函数是excel07版本后新增的一个函数，功能多样且强大，是excel中的一颗璀璨明星，今天我们来看一下这个函数的用法。
语法：=SUMPRODUCT（array1,array2,array3, ...），Array为数组。
意思为在给定的几组数组中，然后把数组间对应的元素相乘，最后返回乘积之和。
SUM意思是求和，PRODUCT意思是求积，组合在一起的意思是乘积之和。
函数的几个经典用法：

操作方法

（01）1、与SUM函数用法比较例：下图中求销售数量总和，SUMPRODUCT(B2:B11)=SUM(B2:B11)求销售总金额：=SUMPRODUCT(B2:B11*C2:C11)回车或者=SUM(B2:B11*C2:C11)按Ctrl+Shife+Enter三键结束

（02）SUMPRODUCT函数支持数组运算，不需要按Ctrl+Shife+Enter三键结束，它的运算原理是当SUMPRODUCT函数的参数为两个数组时，中间可以用乘号也可以是逗号，也可以写成=SUMRPODUCT(B2:B11,C2:C11)数组之间对应元素相乘，再求和，上图中=SUMPRODUCT(B2:B11*C2:C11)=SUMPRODUCT(B2:B11,C2:C11)=B2*C2+B3*C3+B4*C4+······+B11*C11用乘号和用逗号的区别在与，当有一个数组中有文本时，中间用乘号得出错误值，因为文本是无法参与计算的，此时中间只能用逗号隔开，然后文本将会被当成0来处理；

（03）求销售总量，输入=SUMPRODUCT(B2:B11,C2:C11)

（04）2、隔列求和例：求1、2、3、4月份的计划数量之和，在N3输入=SUMPUDUCT((MOD(COLUMN(B3:M3),3)=2)*B3:M3)，下拉填充。

（05）释义：COLUMN函数，返回所选择的单元格的列数，如输入=COLUMN(B5)，则得到结果为2，意思是B5所在的单元格是第二列，输入=COLUMN(G17)，得到结果为7，意思是G17所在的单元格是第7列，COLUMN(B3:M3)，意思是B3到M3所在的列数，得到结果{2,3,4,5,6,7,8,9,10,11,12,13}MOD函数，用来求余数的函数，返回两数相除的余数，输入=MOD(5,2)，得到结果为1，意思是5除以2得到的余数为1，输入=MOD(17,3)，得到结果为2，意思是17除以3得到的余数是2，MOD(COLUMN(B3:M3),3)=2，意思是B3:M3所在的列数除以3，得到余数为2的单元格，得到的结果是{TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE},此中TRUE为真，符合结果，FALSE为假，不符合结果。在计算中TRUE当成1计算，FALSE按0计算计算式：=SUMPRODUCT((MOD(COLUMN(B3:M3),3)=2)*B3:M3)=SUMPRODUCT({1,0,0,1,0,0,1,0,0,1,0,0}*{68,81,13,70,83,13,107,71,-36,85,118,33})=330两个数组之间元素一 一对应相乘再求和。此例中还可以用SUMIF函数，N3中输入=SUMIF($B$2:$M$2,$K$2,B3:M3)下拉填充，注意B2:M2的绝对引用；如果没有“计划、实际、差异”所在的行（删除第二行），则不能用SUMIF函数，用SUMPRDUCT函数较好

（06）3、多条件求和公式用法：=SUMPRODUCT(条件1*条件2*条件3*······条件N) ，公式中多个条件相乘。例：下图中求2017年3月2日宏基21吋电脑销售金额，输入=SUMPRODUCT((A2:A18=--"2017/03/02")*(B2:B18="电脑")*(C2:C18="宏基21吋")*F2:F18)日期前的双负号“--”是对逻辑值进行转换的

（07）求2017年3月3日小米5.5吋手机销售金额，输入=SUMPRODUCT((A2:A18=--"2017/3/3")*(B2:B18="手机")*(C2:C18="小米5.5吋")*F2:F18)，日期前加双负号此时也可以用SUMIFS函数，输入=SUMIFS(F2:F18,A2:A18,"2017/3/3",B2:B18,"手机",C2:C18,"小米5.5吋")下图求东北和西北地区销售总数量，输入=SUMPRODUCT(((A2:A10="东北")+(A2:A10="西北")),B2:B10)

（08）前两个条件相加，=SUMPRODUCT(((A2:A10="东北")+(A2:A10="西北")),B2:B10)=SUMPRODUCT((A2:A10="东北"),B2:B10)+SUMPRODUCT((A2:A10="西北"),B2:B10)4、多条件计数如图输入=SUMPRODUCT((B2:B16>=80)*(C2:C16>=80))

（09）释义：第一个条件B2:B16>=80,当B2:B16区域有符合条件>=80时，显示1，否则显示0，另一条件同理=SUMPRODUCT((B2:B16>=80)*(C2:C16>=80))=SUMPRODUCT({0;1;1;0;1;1;0;0;0;0;0;0;1;1;0}*{1;1;1;1;1;1;0;0;0;0;0;1;0;1;0})两两对应相乘再求和此例还可以用COUNTIFS函数，输入=COUNTIFS(B2:B16,">=80",C2:C16,">=80")5、条件排名下图中，求排名，在C2输入=SUMPRODUCT(($B$2:$B$16>B2)*1)+1向下填充公式含义：在B2:B16区域中，乘以1，把它转化成数组才能参与运算，加1（+1）是看比B2(79)的成绩大的数量有几个，如果有6个，则B2的排名是7；