Excel中有不少萬能函數(shù)，一個函數(shù)能頂多個函數(shù)，例如VLOOKUP、OFFSET、SUBTOTAL、AGGREGATE、SUMPRODUCT等。它們各有專長，功能都非常強大，且受人追捧，今天Excel辦公小課堂來給大家介紹其中的SUMPRODUCT函數(shù)，樸實低調(diào)，不為大多數(shù)人所知，但卻同樣擁有超強的能力！一共整理了10種經(jīng)典案例，非常全面，建議先收藏，不然到時候需要就找不到了哦~SUMPRODUCT函數(shù)：返回相應(yīng)的數(shù)據(jù)或區(qū)域乘積之和功能：SUMPRODUCT(數(shù)組1,數(shù)組2,...)

一、SUMPRODUCT函數(shù)第1種用法：基礎(chǔ)用法，乘積求和

目的：根據(jù)每個商品的數(shù)量和單價，計算所有商品的總計

公式：=SUMPRODUCT(C4:C12,D4:D12)

二、SUMPRODUCT函數(shù)第2種用法：單條件求和

目的：根據(jù)每個商品的數(shù)量和單價，計算行政部使用各商品總計

公式：=SUMPRODUCT((C4:C12="行政部")*(E4:E12)*(F4:F12))

說明：SUMPRODUCT函數(shù)(C4:C12="行政部")表示在C4:C12區(qū)域查詢“行政部”，如果此區(qū)域有包含行政部，則通過行政部中數(shù)量列和單價列的數(shù)據(jù)進行相乘，也就是(E4:E12)*(F4:F12)，最后進行求和匯總。

三、SUMPRODUCT函數(shù)第3種用法：多條件求和

目的：根據(jù)每個商品的數(shù)量和單價，計算行政部6月份使用各商品總計公式：=SUMPRODUCT((C4:C12="行政部")*(D4:D12="6月")*(E4:E12)*(F4:F12))

說明：SUMPRODUCT函數(shù)(C4:C12="行政部")*(D4:D12="6月")表示在C4:C12區(qū)域查詢“行政部”，D4:D12區(qū)域查詢“6月”如果兩個區(qū)域同時包含行政部和6月，則通過行政部和6月的數(shù)量列和單價列數(shù)據(jù)進行相乘，也就是(E4:E12)*(F4:F12)，最后進行求和匯總。

四、SUMPRODUCT函數(shù)第4種用法：單條件計數(shù)

目的：根據(jù)兩個班級總?cè)藬?shù)，統(tǒng)計男同學(xué)人數(shù)

公式：=SUMPRODUCT(N(D4:D13="男"))

說明：SUMPRODUCT函數(shù)N(D4:D13="男")表示在D4:D13區(qū)域計算性別為“男”的個數(shù)，其中“性別”列是文本形式，則需嵌套N函數(shù)，表示返回轉(zhuǎn)化為數(shù)值后的值，從而統(tǒng)計男同學(xué)的人數(shù)。

五、SUMPRODUCT函數(shù)第5種用法：多條件計數(shù)

目的：根據(jù)兩個班級總?cè)藬?shù)，統(tǒng)計80分以上女同學(xué)人數(shù)

公式：=SUMPRODUCT((D4:D13="女")*(E4:E13>80))

說明：SUMPRODUCT函數(shù)(D4:D13="女")表示在D4:D13區(qū)域查詢性別為女的數(shù)據(jù)，(E4:E13>80)表示在E4:E13查詢成績大于80的數(shù)據(jù)，然后將兩則數(shù)據(jù)進行相乘，從而統(tǒng)計80分以上女同學(xué)的人數(shù)。

六、SUMPRODUCT函數(shù)第6種用法：不間斷排名

目的：根據(jù)兩個班級學(xué)生成績進行排名

公式：=SUMPRODUCT(($E$4:$E$13>E4)/COUNTIF($E$4:$E$13,$E$4:$E$13))+1

說明：對于排名可能大家都會想到使用RANK函數(shù)來進行計算排名，但是RANK是國際的一種排名方式，當遇到相同名次時，下一個名次數(shù)字順序就會間斷，例如案例中有三位學(xué)生都是第5名，順延下一位學(xué)生的名次就變成了第8名了，此時就間斷了6和7名的名次，解決不間斷名次（中國式排名方式）需要使用SUMPRODUCT函數(shù)。

SUMPRODUCT函數(shù)($E$4:$E$13>E4)表示在E4:E13成績區(qū)域列是否大于E4成績,按F9則返回一組數(shù)組{FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE}，這里的TRUE表示的是1，F(xiàn)ALSE表示的是0。而COUNTIF($E$4:$E$13,$E$4:$E$13)表示數(shù)組中的元素就是對應(yīng)成績重復(fù)出現(xiàn)的次數(shù)，按F9可以看出{1;1;1;3;3;3;1;1;1;1}，這里的3表示的是成績78分出現(xiàn)3次，1表示沒有重復(fù)的分數(shù)，也就是各分數(shù)出現(xiàn)1次。然后將兩組數(shù)組進行相除，再通過SUMPRODUCT函數(shù)進行求和得到數(shù)字5，后面再加上1表示的是當前數(shù)字的本身排名情況。如果對以上說明不理解，可以通過這種公式套路進行理解

套路：=SUMPRODUCT((條件區(qū)域>條件）/COUNTIF（要進行排名的區(qū)域))+1

七、SUMPRODUCT函數(shù)第7種用法：按條件排名

目的：根據(jù)兩個班級學(xué)生成績，分別進行按班級排名

公式：=SUMPRODUCT(($B$4:$B$13=B4)*($E$4:$E$13>E4))+1

說明：SUMPRODUCT函數(shù)($B$4:$B$13=B4)表示在B4:B13班級區(qū)域列判斷是否等于B4,按F9則返回一組數(shù)組{TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE}，($E$4:$E$13>E4)表示在E4:E13成績區(qū)域列是否大于E4，按F9則返回一組數(shù)組{FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE}。這里的TRUE表示的是1，F(xiàn)ALSE表示的是0，通過SUMPRODUCT函數(shù)將兩組數(shù)組乘積求和匯總，再加上1本身成績排名，從而計算出各班級學(xué)生排名情況。如果對以上說明不理解，可以通過這種公式套路進行理解套路：=SUMPRODUCT((條件區(qū)域1=條件1）*(要進行排名的區(qū)域))+1

八、SUMPRODUCT函數(shù)第8種用法：模糊條件求和

目的：根據(jù)每個商品的數(shù)量和單價，計算筆銷量總和

公式：=SUMPRODUCT(ISNUMBER(FIND("筆",B4:B12))*C4:C12*D4:D12)

說明：由于SUMPRODUCT函數(shù)的參數(shù)中不支持使用通配符，所以在計算帶有關(guān)鍵字的問題，在這里需要截取ISNUMBER解決關(guān)鍵字的問題，首先使用FIND函數(shù)在B4:B12數(shù)據(jù)區(qū)域中查找關(guān)鍵字“筆”，如果包含關(guān)鍵字“筆”，就返回表示關(guān)鍵字位置的數(shù)值，否則就返回錯誤值。然后使用ISNUMBER函數(shù)判斷FIND函數(shù)的數(shù)組結(jié)果是不是數(shù)值，如果是數(shù)值，說明是包含關(guān)鍵字的，如果不是數(shù)值，那就是不包含關(guān)鍵字了。ISNUMBER最終得到由邏輯值TRUE或FALSE構(gòu)成的內(nèi)存數(shù)組，然后再與后面的區(qū)域C4:C12和區(qū)域D4:D12依次相乘，最后由SUMPRODUCT返回乘積之和

九、SUMPRODUCT函數(shù)第9種用法：忽略文本求和

目的：統(tǒng)計所有商品的總數(shù)量

公式：=SUMPRODUCT(C4:C12)

說明：公式SUMPRODUCT(C4:C12)直接對數(shù)量列中包含數(shù)字進行求和，忽略文本。當然在這里也可以直接用SUM函數(shù)進行匯總。

十、SUMPRODUCT函數(shù)第10種用法：數(shù)值和文本混合求和

目的：根據(jù)每個商品的數(shù)量和單價，計算筆銷量總和

公式：=SUMPRODUCT(--SUBSTITUTE(D4:D12,"元","")*C4:C12)

說明：由于單價列包含有單位“元”，此時先使用SUBSTITUTE函數(shù)將單位元進行替換掉，也就是--SUBSTITUTE(D4:D12,"元","")，這里兩個減號--，表示的是負負得正，也就是強制地把文本型的數(shù)字轉(zhuǎn)換成數(shù)值型的，最后通過SUMPRODUCT函數(shù)將數(shù)量列和單價列的數(shù)據(jù)進行乘積求和。

作者：Excel辦公小課堂

今天分享的內(nèi)容，建議收藏起來以備不時之需！

關(guān)注Excel辦公小課堂

解鎖辦公技能，告別無效加班

看完點贊，月入過萬~