編按：

在Excel眾多函數(shù)中，求和函數(shù)永遠是Excel人逃不掉的必學(xué)函數(shù)。一說到求和函數(shù)，大多數(shù)人想到的就是SUM函數(shù)，卻不知道有一個函數(shù)比它強大的多！除了能更輕松的求和，它比SUMIF會條件求和、比COUNTIF會條件計數(shù)，甚至比RANK函數(shù)還能排名……它就是看似只會對乘積求和的SUMPRODUCT函數(shù)！

SUMPRODUCT函數(shù)的功能其實很簡單，就是對乘積進行求和，然而結(jié)合了邏輯值和數(shù)組之后，這個函數(shù)好像變得完全不同了，不僅可以挑戰(zhàn)SUMIF函數(shù)在條件求和領(lǐng)域的權(quán)威，而且還敢于挑戰(zhàn)COUNTIF函數(shù)進行條件計數(shù)，甚至將排名函數(shù)RANK也斬于馬下……

SUM函數(shù)的功能是對所有參數(shù)中的數(shù)字求和，PRODUCT函數(shù)的功能則是對參數(shù)中的所有數(shù)字求積。兩個函數(shù)結(jié)合而成的SUMPRODUCT可以實現(xiàn)對參數(shù)之積求和的功能。例如根據(jù)單價和數(shù)量可以直接計算出總金額，如圖所示。

但這只是SUMPRODUCT函數(shù)的基本功，今天要和大家分享它的十八般武藝，可以說招招精彩。

注：以下案例直接給出公式，有同學(xué)們適用的場景可以直接套用。

Ps：關(guān)于SUMPRODUCT函數(shù)的用法和原理，之前有過很多篇教程可以參考，今天就不詳細解釋每個公式了。

SUMPRODUCT第一招：條件求和

如圖，對7月的銷售數(shù)量求和，公式為：=SUMPRODUCT((A2:A15="7月")*C2:C15)

可能你會說，這個SUMIF也能干啊。別急，看看這樣的條件求和SUMIF能干嗎？

SUMPRODUCT第二招：秒了SUMIF的條件求和方法

如圖，數(shù)據(jù)源里沒有月份只有具體的銷售日期，試問你能用SUMIF求和嗎？

SUMPRODUCT是這樣干的：=SUMPRODUCT((MONTH(A2:A15)=10)*C2:C15)

SUMIF干瞪眼沒招了，SUMPRODUCT接著秀，還有多列的條件求和呢。

SUMPRODUCT第三招：針對多列的條件求和

如圖，要求和的數(shù)據(jù)位于多列。

公式為：=SUMPRODUCT(($A$2:$A$22=I2)*$C$2:$G$22)

看到這個問題，SUMIF也想試試，但好像一個SUMIF搞不定，得來五個……

而SUMPRODUCT又跑去找SUMIFS的麻煩了。

SUMPRODUCT第四招：多條件求和

如圖，按照銷售機構(gòu)和商品名稱對銷售數(shù)量求和，這是SUMIFS的老本行，但SUMPRODUCT是這樣干的：=SUMPRODUCT($C$2:$C$22*($A$2:$A$22=$E2)*($B$2:$B$22=F$1))

SUMIFS一個勁的翻白眼，SUMPRODUCT跳的更歡了，繼續(xù)表演多列多條件求和。

SUMPRODUCT第五招：針對多列的多條件求和

如圖，要求滿足兩個條件（機構(gòu)和商品名稱）后，并將求和的結(jié)果顯示到不同列中。SUMPRODUCT是這么干的：

=SUMPRODUCT($C$2:$G$22*($A$2:$A$22=$I2)*($B$2:$B$22=J$1))

看著SUMPRODUCT的一波操作，SUMIFS一臉懵逼。

遠處，COUNTIF和COUNTIFS為好友SUMIFS出面，沒想到SUMPRODUCT二話不說，直接秀出一波連環(huán)攻擊。

SUMPRODUCT第六——第十三招：條件計數(shù)

數(shù)據(jù)源如圖，涉及四類條件計數(shù)問題，每個問題兩個公式，共八個公式。

統(tǒng)計女性人數(shù)：

公式1：=SUMPRODUCT(N(B2:B20=G2))

公式2：=SUMPRODUCT(N(B2:B20="女"))

統(tǒng)計男性本科人數(shù)：

公式1：=SUMPRODUCT((B2:B20=G4)*(C2:C20=G5))

公式2：=SUMPRODUCT((B2:B20="男")*(C2:C20="本科"))

統(tǒng)計30歲以上的人數(shù)：

公式1：=SUMPRODUCT(--(D2:D20>G6))

公式2：=SUMPRODUCT(--(D2:D20>30))

統(tǒng)計30歲到40歲之間的人數(shù)（含40歲）：

公式1：=SUMPRODUCT((D2:D20>G8)*(D2:D20<=G9))

公式2：=SUMPRODUCT((D2:D20>30)*(D2:D20<=40))

看了這幾個方法，COUNTIF和COUNTIFS不得不服氣，指著更遠處的RANK，朝SUMPRODUCT努努嘴，想看SUMPRODUCT還能不能一路秀下去。

RANK正在處理一個排名次的問題。

SUMPRODUCT好奇的看著，覺得有點新鮮，沉思了一會，秀出了一招。

SUMPRODUCT第十四招：排名次

公式為：=SUMPRODUCT(N($B$2:$B$9>B2))+1

SUMPRODUCT高興地沖著RANK說了句，你不行啊，有兩個第五名，第六名被你干沒了，直接就到第七名了。

RANK回了句，你行你上啊。

只見SUMPRODUCT朝著COUNTIF招了招手說，兄弟來搭把手。

SUMPRODUCT第十五招：中國式排名，我也行

公式為：=SUMPRODUCT((B$2:B$9>=B6)/COUNTIF(B$2:B$9,B$2:B$9))

RANK不服氣的說，你找人幫忙了，不算。

SUMPRODUCT挑了挑眉毛，大喊一聲，不找人也行，我還會分組排名和綜合排名，不信你看。

SUMPRODUCT第十六招：分組排名

公式為：=SUMPRODUCT((A$2:A$9=A2)*(C$2:C$9>=C2))

RANK大吃一驚，一時間無法相信自己看到的一切，SUMPRODUCT還在繼續(xù)表演。

SUMPRODUCT第十七招：綜合排名

鑒于這個問題比較復(fù)雜，要對規(guī)則做一說明，即多權(quán)重綜合排名常用于有多項考核指標(biāo)同時存在的情況，需要根據(jù)每個指標(biāo)的重要程度與結(jié)果進行綜合排名。

例如，下圖數(shù)據(jù)中，需要同時考慮業(yè)績增長率（最重要）、任務(wù)達成率（其次重要）和投訴解決率（第三重要）三項指標(biāo)，可以使用公式：

=SUMPRODUCT(N(B$2:B$8*10000+C$2:C$8*10+D$2:D$8>=B2*10000+C2*10+D2))

看著毫無還手之力的RANK，SUMPRODUCT對COUNTIF說，最后一招還是咱們兄弟兩一起吧。

SUMPRODUCT第十八招：統(tǒng)計不重復(fù)個數(shù)

有若干條銷售數(shù)據(jù)，需要統(tǒng)計出有幾位銷售人員。

公式為=SUMPRODUCT(1/COUNTIF(B2:B15,B2:B15))

十八招表演完畢，就問你SUMPRODUCT厲害不厲害。

其實只要是函數(shù)就有死穴，而SUMPRODUCT函數(shù)就是：數(shù)組參數(shù)必須具有相同的維數(shù)，否則將返回#VALUE!錯誤值。

另外SUMPRODUCT函數(shù)還有三個小秘密：

1、函數(shù)SUMPRODUCT將非數(shù)值型的數(shù)組元素作為0處理；

2、函數(shù)SUMPRODUCT不能使用通配符，但可以與函數(shù)FIND組合實現(xiàn)包含功能；

3、函數(shù)SUMPRODUCT是不用按三鍵結(jié)束的數(shù)組運算函數(shù)。

今天的內(nèi)容看的過癮嗎，記得轉(zhuǎn)發(fā)哦。

本文配套的練習(xí)課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時隨地學(xué)習(xí)Excel：

相關(guān)推薦：

Excel必會函數(shù)之SUMPRODUCT函數(shù)

加了*的 SUMPRODUCT函數(shù)無所不能

sumif和countif函數(shù)應(yīng)用

TEXT和SUMPRODUCT強強聯(lián)合，只為解決一個“微不足道”的編號問題？？

版權(quán)申明：

本文作者郅龍；同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。