隨著SUMIFS、COUNTIFS等一些函數(shù)的出現(xiàn)，函數(shù)SUMPRODUCT漸漸被遺忘，由于前者的運(yùn)行速度比他快而被慢慢淡出了excel接觸者的視線。然并卵，我想說函數(shù)SUMPRODUCT的強(qiáng)大功能足以讓函數(shù)SUMIFS、COUNTIFS等失業(yè)，而數(shù)組公式函數(shù)SUMPRODUCT能取代函數(shù)SUM并且不需要按<Ctrl Shift Enter>三鍵結(jié)束。
一、函數(shù)語法解析
1、函數(shù)定義：
在給定的幾組數(shù)組中，將數(shù)組間對應(yīng)的元素相乘，并返回乘積之和。
2、語法格式：
SUMPRODUCT(array1,[array2],[array3],...)
SUMPRODUCT(數(shù)組區(qū)域1，數(shù)組區(qū)域2，數(shù)組區(qū)域3，……)
3、參數(shù)說明：
array1：必需。其相應(yīng)元素需要進(jìn)行相乘并求和的第一個數(shù)組參數(shù)。
array2，array3，...：可選。2到255個數(shù)組參數(shù)，其相應(yīng)元素需要進(jìn)行相乘并求和。
二、擴(kuò)展語法
1、多條件計數(shù)語法：
SUMPRODUCT((條件1區(qū)域='條件1')*(條件2區(qū)域='條件2')*(……))
2、多條件求和語法：
①、SUMPRODUCT((條件1區(qū)域='條件1')*(條件2區(qū)域='條件2')*(……)*求和區(qū)域)
②、SUMPRODUCT((條件1區(qū)域='條件1')*(條件2區(qū)域='條件2')*(……),求和區(qū)域)
3、求和語法中求和區(qū)域前使用“,”與“*”的區(qū)別：
多條件求和時，當(dāng)求和區(qū)域中沒有文本時“,”與“*”可以通用；
當(dāng)求和區(qū)域中有文本時使用“*”會返回錯誤值#VALUE!，故而只能使用“,”。
三、函數(shù)示例
1、基礎(chǔ)應(yīng)用
▲
01
文本數(shù)字求和
輸入公式：=SUMPRODUCT(--(B3:B9))
解析：該公式中兩個負(fù)號，一個負(fù)號是把文本型數(shù)字轉(zhuǎn)換成負(fù)數(shù)值,另一個負(fù)號是把負(fù)數(shù)值轉(zhuǎn)換成需要的正數(shù)。
當(dāng)然轉(zhuǎn)換的方法有很多種，可以用“ 0”、“-0”、“*1”、“/1”、“^1”等等，關(guān)鍵看你自己怎么用。
有人覺得用函數(shù)SUMPRODUCT字符太長了，用函數(shù)SUM明顯字符少很多，該題中用函數(shù)SUM代替函數(shù)SUMPRODUCT，那就必須按Ctrl Shift Enter三鍵結(jié)束。
▲
02
忽略文本求和
輸入公式：=SUMPRODUCT(A15:A22)
▲
03
有文本的數(shù)組相乘
輸入公式：
=SUMPRODUCT(C29:C34,D29:D34)
解析：函數(shù)SUMPRODUCT將非數(shù)值型的數(shù)組元素作為0處理。
如果該題中用單一的函數(shù)SUM來解決，按三鍵結(jié)束是小事，會出現(xiàn)錯誤值#VALUE!。
函數(shù)SUM的參數(shù)為不能轉(zhuǎn)換成數(shù)字的文本,將會顯示錯誤。
函數(shù)SUM的解決方法：=SUM(IFERROR(C29:C34*D29:D34,''))，按三鍵結(jié)束。
▲
04
單條件計數(shù)數(shù)
輸入公式：=SUMPRODUCT(--(B41:D47>=80))
解析：(B41:D47>=80)部分判斷條件是否成立，成立返回TRUE，不成立返回FALSE，
發(fā)生運(yùn)算時TRUE相當(dāng)于1，F(xiàn)ALSE相當(dāng)于0，
然后用函數(shù)SUMPRODUCT或函數(shù)SUM求和，
注意使用函數(shù)SUM時一定要按三鍵結(jié)束。
同樣的可以用“ 0”、“-0”、“*1”、“/1”、“^1”等等來轉(zhuǎn)換。
▲
05
單條件求和
輸入公式：
=SUMPRODUCT((B54:D60>=80)*B54:D60)
解析：(B54:D60>=80)部分判斷條件是否成立，成立返回TRUE，不成立返回FALSE，
再*B54:D60得到的就是滿足條件的，
最后用函數(shù)SUMPRODUCT或函數(shù)SUM求和，
注意使用函數(shù)SUM時一定要按三鍵結(jié)束。
得到邏輯值TRUE和FALSE后直接*B54:D60就已發(fā)生運(yùn)算，如果像上題一樣用“ 0”、“-0”、“*1”、“/1”、“^1”等等來轉(zhuǎn)換雖不影響結(jié)果，但明顯是多此一舉。
2、進(jìn)階應(yīng)用
▲
01
單條件數(shù)組相乘
輸入公式：=SUMPRODUCT((A3:A8='燒烤')*C3:C8*D3:D8)
或者：=SUMPRODUCT((A3:A8='燒烤')*C3:C8,D3:D8)
▲
02
多條件計數(shù)
輸入公式：=SUMPRODUCT((B15:B21='女')*(C15:C21>=60))
▲
03
多條件求和
輸入公式：=SUMPRODUCT((B28:B34='女')*(C28:C34>=60)*C28:C34)
或者：=SUMPRODUCT((B28:B34='女')*(C28:C34>=60),C28:C34)
▲
04
有條件的區(qū)間計數(shù)
輸入公式：=SUMPRODUCT((B41:B47='女')*(C41:C47>=60)*(C41:C47<=80))
▲
05
有條件的區(qū)間求和
輸入公式：=SUMPRODUCT((B54:B60='女')*(C54:C60>=60)*(C54:C60<=80)*C54:C60)
或者：=SUMPRODUCT((B54:B60='女')*(C54:C60>=60)*(C54:C60<=80),C54:C60)
上述示例可以參照前面說的擴(kuò)展語法格式來，也可以用函數(shù)SUMIFS、COUNTIFS來完成，關(guān)于這兩個函數(shù)的用法不再贅述，也可以用函數(shù)SUM代替函數(shù)SUMPRODUCT，需要注意的是要按三鍵結(jié)束。
3、組合應(yīng)用
▲
01
隔列求和
輸入公式：
=SUMPRODUCT((MOD(COLUMN(B:I),2)=0)*B3:I3)
▲
02
零鈔的計算
注：第一行中的“元”是通過設(shè)置單元格格式自定義加上去的，若是直接輸入的“元”就要先提取數(shù)字或把“元”替換掉。
在C15單元格輸入公式：
=INT(($B15-SUMPRODUCT($A$14:B$14,$A15:B15) 1%%)/C$14)，向右向下填充。
思路：當(dāng)前面額張數(shù)=(工資-(已計算面額*已計算面額張數(shù)))/當(dāng)前面額
解析：SUMPRODUCT($A$14:B$14,$A15:B15)部分算出了左側(cè)單元格中已經(jīng)計算張數(shù)的金額，用B列的工資減去這個金額，然后除以當(dāng)前的面額，再用INT函數(shù)取整就可以得到對應(yīng)面額的鈔票數(shù)量。
留個問題：為什么要加1%%呢？加1%、1%%%等可不可以呢？不加又可不可以呢？
▲
03
多條件求不重復(fù)的個數(shù)
輸入公式：
=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)
解析：函數(shù)SUMPRODUCT不能使用通配符，故而與函數(shù)FIND組合實現(xiàn)包含求和功能。
函數(shù)SUMIFS通配符解法：=SUMIFS(C57:C62,A57:A62,'*6*',B57:B62,'男')
②、求姓名中有波形符~的銷售額之和
輸入公式：
=SUMPRODUCT(ISNUMBER(FIND('~',A57:A62))*C57:C62)
解析：函數(shù)SUMPRODUCT不能使用通配符，故而與函數(shù)FIND組合實現(xiàn)包含求和功能。
函數(shù)SUMIF通配符解法：
=SUMIF(A57:A62,'*~~*',C57:C62)
四、注意事項
①、數(shù)組參數(shù)必須具有相同的維數(shù)。否則，函數(shù)SUMPRODUCT將返回#VALUE!錯誤值。
②、函數(shù)SUMPRODUCT將非數(shù)值型的數(shù)組元素作為0處理。
③、函數(shù)SUMPRODUCT不能使用通配符，但可以與函數(shù)FIND組合實現(xiàn)包含功能。
④、函數(shù)SUMPRODUCT是不用按<Ctrl Shift Enter>三鍵結(jié)束的數(shù)組運(yùn)算函數(shù)。
作者：仰望星空
