SUMPRODUCT函數(shù)用法詳解Word版

1、Excel多條件求和 & SUMPRODUCT函數(shù)用法詳解轉(zhuǎn)Excel多條件求和 & SUMPRODUCT函數(shù)用法詳解龍逸凡日常工作中，我們經(jīng)常要用到多條件求和，方法有多種，第一類：使用基本功能來實現(xiàn)。主要有：篩選、分類匯總、數(shù)據(jù)透視表、多條件求和向?qū)?；第二類：使用公式來實現(xiàn)方法。主要有：使用SUM函數(shù)編寫的數(shù)組公式、聯(lián)用SUMIF和輔助列（將多條件變?yōu)閱螚l件）、使用SUMPRODUCT函數(shù)、使用SUMIFS函數(shù)(限于Excel2007及以上的版本)，方法千差萬別、效果各有千秋。本人更喜歡用SUMPRODUCT函數(shù)。由于Excel幫助對SUMPRODUCT函數(shù)的解釋太簡短了，與

2、SUMPRODUCT函數(shù)的作用相比實在不匹配，為了更好地掌握該函數(shù)，特將其整理如下。龍逸凡注：歡迎轉(zhuǎn)貼，但請注明作者及出處。一、 基本用法在給定的幾組數(shù)組中，將數(shù)組間對應(yīng)的元素相乘，并返回乘積之和。語法：SUMPRODUCT(array1,array2,array3, .)Array1, array2, array3, . 為 2 到 30 個數(shù)組，其相應(yīng)元素需要進行相乘并求和。公式：=SUMPRODUCT(A2:B4, C2:D4) A B C D1 Array 1 Array 1 Array 2 Array 22 3 4 2 73 8 6 6 74 1 9 5 3 公式解釋：兩個數(shù)組的所有

3、元素對應(yīng)相乘，然后把乘積相加，即 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3。計算結(jié)果為156二、 擴展用法1、 使用SUMPRODUCT進行多條件計數(shù)語法：1 / 14SUMPRODUCT(條件1)*(條件2)*(條件3)* (條件n)作用：統(tǒng)計同時滿足條件1、條件2到條件n的記錄的個數(shù)。實例：=SUMPRODUCT(A2:A10="男")*(B2:B10="中級職稱")公式解釋：統(tǒng)計性別為男性且職稱為中級職稱的職工的人數(shù)2、 使用SUMPRODUCT進行多條件求和語法：SUMPRODUCT(條件1)*(條件2)* (條件3)

4、*(條件n)*某區(qū)域)作用：匯總同時滿足條件1、條件2到條件n的記錄指定區(qū)域的匯總金額。實例：=SUMPRODUCT(A2:A10="男")*(B2:B10="中級職稱")*C2:C10)公式解釋：統(tǒng)計性別為男性且職稱為中級職稱的職工的工資總和（假設(shè)C列為工資） 贊 4 2010-12-20 00:01回復 catyoung18 4位粉絲 2樓三、 注意事項1、數(shù)組參數(shù)必須具有相同的維數(shù)，否則，函數(shù) SUMPRODUCT 將返回錯誤值 #VALUE!。 2、SUMPRODUCT函數(shù)將非數(shù)值型的數(shù)組元素作為 0 處理。3、在SUMPRODUCT中，2003

5、及以下版本不支持整列（行）引用，必須指明范圍，不可在SUMPRODUCT函數(shù)使用A:A、B:B，Excel2007及以上版本可以整列（列）引用，但并不建議如此使用，公式計算速度慢。4、SUMPRODUCT函數(shù)不支持“*”和“？”通配符SUMPRODUCT函數(shù)不能象SUMIF、COUNTIF等函數(shù)一樣使用“*”和“？”等通配符，要實現(xiàn)此功能可以用變通的方法，如使用LEFT、RIGHT、ISNUMBER(FIND()或ISNUMBER(SEARCH()等函數(shù)來實現(xiàn)通配符的功能。如：=SUMPRODUCT(A2:A10="男")*(B2:B10="中級職稱")

6、*(LEFT(D2:D10,1)="龍")*C2:C10)=SUMPRODUCT(A2:A10="男")*(B2:B10="中級職稱")*(ISNUMBER(FIND("龍逸凡",D2:D10)*C2:C10)注：以上公式假設(shè)D列為職工姓名。ISNUMBER(FIND()、ISNUMBER(SEARCH()作用是實現(xiàn)“*”的通配功能，只是前者區(qū)分大小寫，后者不區(qū)分大小寫。5、SUMPRODUCT函數(shù)多條件求和時使用“，”和“*”的區(qū)別：當擬求和的區(qū)域中無文本時兩者無區(qū)別，當有文本時，使用“*”時會出錯，返回錯誤值

7、#VALUE!，而使用“，”時SUMPRODUCT函數(shù)會將非數(shù)值型的數(shù)組元素作為 0 處理，故不會報錯。 也就是說：公式1：=SUMPRODUCT(A2:A10="男")*(B2:B10="中級職稱")*C2:C10)公式2：=SUMPRODUCT(A2:A10="男")*(B2:B10="中級職稱")，C2:C10)當C2:C10中全為數(shù)值時，兩者計算結(jié)果一樣，當C2:C10中有文本時公式1會返回錯誤值 #VALUE!，而公式2會返回忽略文本以后的結(jié)果。四、 網(wǎng)友們的精彩實例1、求指定區(qū)域的奇數(shù)列的數(shù)值之和=SU

8、MPRODUCT(MOD(COLUMN(A1:F1),2)*A1:F1)2、求指定區(qū)域的偶數(shù)行的數(shù)值之和 =SUMPRODUCT(MOD(ROW(A1:A22),2)-1)*A1:A22)*(-1)3、求指定行中列號能被4整除的列的數(shù)值之和 =SUMPRODUCT(MOD(COLUMN(A1:P1),4)=0)*A1:P1)4、.求某數(shù)值列前三名分數(shù)之和SUMPRODUCT(LARGE(B1:B16,ROW(1:3)5、統(tǒng)計指定區(qū)域不重復記錄的個數(shù)SUMPRODUCT(1/COUNTIF(V11:V15,V11:V15)QUOTE:以下是引用tonycq在2008-1-3 17:32:46的發(fā)

9、言：這里我僅談該函數(shù)的兩個關(guān)鍵問題未涉及其它查找函數(shù)，感謝gdliyy版主對文中不妥之處的指正。針對您前面對SUMPRODUCT的描述，我再補充以下幾點，歡迎大家共同討論！3、對于SUMPRODUCT函數(shù)，原來曾經(jīng)有些討論，這里再補充一下：a)如果所有參數(shù)使用逗號分隔，那么限制就比較多，要求如下：（以下簡單以引用為例）一維引用的維度必須相同，即：sumproduct(a1:a5,b1:f1)，結(jié)果錯誤，因為一維引用的方向不同；相同維度引用的尺寸大小必須相同，即：sumproduct(a1:a5,b1:b6)，結(jié)果錯誤，因為前一引用為5個元素，后一引用為6個元素；二維引用由于涉及一個平面，因此遵

10、循第2個原則尺寸相同：即：sumproduct(a1:b5,c2:d6)，必須都是5行2列，而不是原LZ說的，只要面積相同就可行，如下面的公式結(jié)果錯誤：sumproduct(a1:b5,c1:g2)，因為前引用為5行2列，而后引用為2行5列，雖然單元格總個數(shù)相同，但實際的引用尺寸是完全不同的。b)如果函數(shù)中不用逗號分隔，而使用連乘方式來進行條件求和，那就遵循另外的規(guī)則：（仍以引用為例）兩個不同維度的一維引用可以運算，即：sumproduct(a1:a5*b1:f1)，結(jié)果是對生成的5行5列的新數(shù)組進行求和；相同維度引用的尺寸大小必須相同，同數(shù)組相乘運算法則，即：sumproduct(a1:a5

11、*b1:b6)，因為引用大小不同，導致最后一行單元格相乘得出#N/A錯誤。同樣的，如果兩個二維引用相乘，也是遵循數(shù)組運算的法則，兩個引用的尺寸大小必須相同，即：sumproduct(a1:b5*c2:d6)，結(jié)果為分別對應(yīng)相乘后的5行2列的新數(shù)組進行求和，而sumproduct(a1:b5*c1:g2)的結(jié)果為錯誤，因此兩引用尺寸不同。最后，這類數(shù)組（引用）的運算還可以綜合應(yīng)用，也是正確的，如：=SUMPRODUCT(a2:a6*b1:c1*d2:e6)參數(shù)內(nèi)部運算結(jié)果最終將得到一個新的數(shù)組，即：5行2列，最終由SUMPRODUCT求和得出結(jié)果。最后補充一點：對于SUMPRODUCT函數(shù)的用法

12、有很多技巧，利用數(shù)組（引用）相乘運算，可以避免維度不同的錯誤；但利用逗號分隔的用法，可以避免文本參與乘法運算出現(xiàn)錯誤，大家應(yīng)該綜合考慮來進行運用。如：統(tǒng)計各部門男性員工的工資總額范例：=Sumproduct(部門=指定部門)*(性別="男"),工資)這樣就可以避免很多問題，容錯性也好得多。SUMPRODUCT函數(shù)目錄含義 語法 說明 示例編輯本段含義漢語的意思是 SUM:【數(shù)】求和 PRODUCT:【數(shù)】(乘)積 20 is the product of 5 and 4.二十是五與四的乘積。 SUMPRODUCT:組合的漢語意思是:乘積之和 在給定的幾組數(shù)組中，將數(shù)組間對應(yīng)

13、的元素相乘，并返回乘積之和。 編輯本段語法SUMPRODUCT（array1,array2,array3, .） Array1,array2,array3, . 為 2 到 30 個數(shù)組，其相應(yīng)元素需要進行相乘并求和。 編輯本段說明· 數(shù)組參數(shù)必須具有相同的維數(shù)，否則，函數(shù) SUMPRODUCT 將返回錯誤值 #VALUE!。 · 函數(shù) SUMPRODUCT 將非數(shù)值型的數(shù)組元素作為 0 處理。 編輯本段示例如果將示例復制到空白工作表中，可能會更易于理解該示例。 BCDE1Array1Array1Array2Array2234273866741953公式說明（結(jié)果）=SUM

14、PRODUCT(B2:C4*D2:E4)兩個數(shù)組的所有元素對應(yīng)相乘，然后把乘積相加，即3*2+4*7+8*6+6*7+1*5+9*3。(156)數(shù)學函數(shù)SUMPRODUCT應(yīng)用實例 一、基本功能 1函數(shù)SUMPRODUCT的功能返回相應(yīng)的區(qū)域或數(shù)組乘積的和。 2基本格式SUMPRODUCT（數(shù)據(jù)1，數(shù)據(jù)2，數(shù)據(jù)30）3示例數(shù)據(jù)表A列 B列 C列 D列 E列數(shù)據(jù)1數(shù)據(jù)2數(shù)據(jù)3數(shù)據(jù)4數(shù)據(jù)52 3 4 12 105 5 6 5 209 7 8 #N/A 307 2 7 9 KL1 6 2 8 2 基本計算 區(qū)域計算要求：計算A、B、C三列對應(yīng)數(shù)據(jù)乘積的和。公式：=SUMPRODUCT(A2:A6,B

15、2:B6,C2:C6)計算方式：=A2*B2*C2+A3*B3*C3+A4*B4*C4+A5*B5*C5+A6*B6*C6即三個區(qū)域A2:A6,B2:B6,C2:C6同行數(shù)據(jù)積的和。返回值788。 數(shù)組計算要求：把上面數(shù)據(jù)表中的三個區(qū)域A2:A6,B2:B6,C2:C6數(shù)據(jù)按一個區(qū)域一個數(shù)組，計算對應(yīng)數(shù)組積的和。把A2:A6,B2:B6,C2:C6分別作為一個數(shù)組,即A2:A6表示為數(shù)組-2;5;9;7;1B2:B6表示為數(shù)組-3;5;7;2;6C2:C6表示為數(shù)組-4;6;8;7;2公式： =SUMPRODUCT(2;5;9;7;1,3;5;7;2;6,4;6;8;7;2) =788 注意：

16、數(shù)組數(shù)據(jù)用大括號括起來。行數(shù)據(jù)之間用分號"；"分隔，如果是同一行的數(shù)據(jù)，用逗號","分隔。 可能出現(xiàn)的錯誤 編輯公式時，引用的數(shù)據(jù)區(qū)域大小不一致導致計算錯誤，返回值為#VALUE！。示例：在上面的數(shù)據(jù)表中，計算A列與B列數(shù)據(jù)區(qū)域積的和。公式： =SUMPRODUCT(A2:A6,B2:B5) 或 =SUMPRODUCT(A2:A6,B2:B8) 都會返回錯誤值#VALUE！。所以在用SUMPRODUCT函數(shù)時，引用的數(shù)據(jù)區(qū)域大小要一致。 數(shù)據(jù)區(qū)域中有錯誤值時，計算出現(xiàn)錯誤值。示例：在上面的數(shù)據(jù)表中，計算數(shù)據(jù)區(qū)域A2：A6與D2：D6對應(yīng)積的和。公式： =

17、SUMPRODUCT(A2:A6,D2:D6) 因為D2：D6中有錯誤值#N/A，所以公式返回值為錯誤值#N/A。 數(shù)據(jù)區(qū)域引用不能整列引用。示例：計算上面數(shù)據(jù)表中A2：A6和B2：B6區(qū)域?qū)?yīng)數(shù)據(jù)積的和，正確公式為=SUMPRODUCT(A2:A6,B2:B6)則返回正確的計算值94。如果用公式 =SUMPRODUCT(A:A,B:B) 則返回錯誤值#NUM！。 數(shù)據(jù)區(qū)域有文本，計算中系統(tǒng)默認文本值為0。示例：在上面數(shù)據(jù)表中，計算A2：A6和E2：E6區(qū)域中對應(yīng)數(shù)據(jù)積的和。公式 =SUMPRODUCT(A2:A6,E2:E6) 返回值是392，其中E5是文本KL，則A5*E5=0。 二、用于

18、多條件計數(shù)用數(shù)學函數(shù)SUMOPRODUCT計算符合2個及以上條件的數(shù)據(jù)個數(shù) 數(shù)據(jù)表如下數(shù)據(jù)表所示： ABC1姓名性別職稱2A男中一3B女中二4C女中一5D男中一6E女中一7F男中二8G女中二9H男中一10I男中一11J女中一統(tǒng)計表E列 F列 G列中一 中二男女要求：統(tǒng)計上面數(shù)據(jù)表中男、女性中分別是中一、中二的人數(shù)。如下圖片，A1：C11數(shù)據(jù)區(qū)域，在E1：G3區(qū)域統(tǒng)計男、女中中一和中二的人數(shù)是多少。    計算結(jié)果公式在數(shù)據(jù)統(tǒng)計區(qū)域中的F2單元格編輯如下公式： =SUMPRODUCT($B$2:$B$11=$E2)*($C$2:$C$11=F$1) 向下復制到F3，向右復制到

19、G3。 公式分解及分析 條件1$B$2:$B$11=$E2在計算過程中，條件1是一個數(shù)組，返回多值，寫成公式如下：=$B$2:$B$11=$E2具體操作：選中10個連續(xù)的單元格，輸入上述公式后，按Ctrl+Shift+回車鍵確認，返回10個邏輯判斷值TRUE；FALSE；FALSE；TRUE；FALSE；TRUE；FALSE；TRUE；TRUE；FALSE。這10個邏輯判斷值構(gòu)成一個新的由TRUE主FALSE組成的數(shù)組1。 條件2$C$2:$C$11=F$1與條件1相同，是一個數(shù)組，返回多值，寫成公式如下：=$C$2:$C$11=F$1具體操作：同樣的方法，選中對應(yīng)的10連續(xù)單元格，輸入上述公

20、式，按Ctrl+Shift+回車鍵確認，返回10個邏輯判斷值TRUE；FALSE；TRUE；TRUE；TRUE；FALSE； FALSE；TRUE； TRUE；TRUE。這10個邏輯判斷值構(gòu)成另一個新的由TRUE主FALSE組成的數(shù)組2。 ($B$2:$B$11=$E2)*($C$2:$C$11=F$1)由新構(gòu)成的數(shù)組1乘以數(shù)組2，即： =($B$2:$B$11=$E2)*($C$2:$C$11=F$1) =數(shù)組1*數(shù)組2 = TRUE；FALSE；FALSE；TRUE；FALSE；TRUE；FALSE；TRUE；TRUE；FALSE * TRUE；FALSE；TRUE；TRUE；TRUE；F

21、ALSE； FALSE；TRUE； TRUE；TRUE =TRUE*TRUE;FALSE*FALSE;FALSE*TRUE;TRUE*TRUE;FALSE*TRUE;TRUE*FALSE; FALSE*FALSE;TRUE*TRUE;TRUE*TRUE; FALSE*TRUE =1;0;0;1;0;0;0;1;1;0 其中,邏輯值TRU*與*LSE參與計算時: TRUE=1，F(xiàn)ALSE=0，TRUE*TRUE=1，TRUE*FALSE=FALSE*TRUE=0，F(xiàn)ALSE*FALSE=0 因此數(shù)組1*數(shù)組2=1;0;0;1;0;0;0;1;1;0由1和0構(gòu)成了一個新的數(shù)組3。 =SUMPROD

22、UCT($B$2:$B$11=$E2)*($C$2:$C$11=F$1) 函數(shù)SUMPRODUCT對新的數(shù)組3中的所有數(shù)據(jù)求和。即： =SUMPRODUCT($B$2:$B$11=$E2)*($C$2:$C$11=F$1)=SUMPRODUCT(數(shù)組1*數(shù)組2) =SUMPRODUCT(數(shù)組3) =SUMPRODUCT(1;0;0;1;0;0;0;1;1;0) =4 這里需要說明的是，公式編輯按照函數(shù)SUMPRODUCT的一般格式，可以編輯如下等效的公式： =SUMPRODUCT($B$2:$B$11=$E2)*1，($C$2:$C$11=F$1)*1) 函數(shù)SUMPRODUCT的作用是對數(shù)組

23、($B$2:$B$11=$E2)與數(shù)組($C$2:$C$11=F$1)計算其乘積的和，即： =SUMPRODUCT($B$2:$B$11=$E2)*1，($C$2:$C$11=F$1)*1) =SUMPRODUCT( TRUE；FALSE；FALSE；TRUE；FALSE；TRUE；FALSE；TRUE；TRUE；FALSE *1， TRUE；FALSE；TRUE；TRUE；TRUE；FALSE； FALSE；TRUE； TRUE；TRUE *1) =SUMPRODUCT(1;0;0;1;0;1;0;1;1;0,1;0;1;1;1;0;0;1;1;1) =SUMPRODUCT(1*1;0*0;

24、0*1;1*1;0*1;1*0;0*0;1*1;1*1,0*1) =SUMPRODUCT(1;0;0;1;0;0;0;1;1,0) =4 注意：TRUE*1=1，F(xiàn)ALSE*1=1*FALSE=0，TRUE*0=0*TRUE=0 。數(shù)組中用分號分隔，表示數(shù)組是一列數(shù)組，分號相當于換行。兩個數(shù)組相乘是同一行的對應(yīng)兩個數(shù)相乘。 三、用于多條件求和對于計算符合某一個條件的數(shù)據(jù)求和，可以用SUMIF函數(shù)來解決。如果要計算符合2個以上條件的數(shù)據(jù)求和，用SUMIF函數(shù)就不能夠完成了。這就可以用函數(shù)SUMPRODUCT。 用函數(shù)SUMPRODUCT計算符合多條件的數(shù)據(jù)和，其基本格式是：SUMPRODUCT（

25、條件1*條件2*，求和數(shù)據(jù)區(qū)域） 數(shù)據(jù)表 ABCD1姓名性別職稱課時2A男中一153B女中二164C女中一145D男中一136E女中一187F男中二158G女中二169H男中一1410I男中一1711J女中一18要求：計算男、女分別是中一或中二的總課時數(shù)。 統(tǒng)計表F列 G列 H列 中一 中二男女在G2中編輯公式 =SUMPRODUCT($B$2:$B$11=$F2)*($C$2:$C$11=G$1),$D$2:$D$11) 回車確認后向下向右復制公式到H3單元格。 公式釋義：性別區(qū)域$B$2:$B$11中滿足條件男和職稱區(qū)域$C$2:$C$11中滿足條件中一的數(shù)據(jù)，通過判斷計算后由1和0組成一

26、個新的數(shù)據(jù)區(qū)域，這個新的數(shù)據(jù)區(qū)域再和課時區(qū)域$D$2:$D$11中的對應(yīng)數(shù)據(jù)相乘后求和。 公式對比：到此，對函數(shù)SUMPRODUCT用來計數(shù)和求和，試作一對比 計數(shù)公式 =SUMPRODUCT($B$2:$B$11=$E2)*($C$2:$C$11=F$1) 求和公式 =SUMPRODUCT($B$2:$B$11=$F2)*($C$2:$C$11=G$1),$D$2:$D$11) 不難看出，求和公式在原來的計數(shù)公式中，在相同判斷條件下，增加了一個求和的數(shù)據(jù)區(qū)域。也就是說，用函數(shù)SUMPRODUCT求和，函數(shù)需要的參數(shù)一個是進行判斷的條件，另一個是用來求和的數(shù)據(jù)區(qū)域。 四、用于排名次如下A列數(shù)據(jù)

27、：A列 B列數(shù)據(jù) 名次56658965567890526090需要排出區(qū)域A2：A11中10個數(shù)據(jù)的名次。在B2中編輯公式： =SUMPRODUCT(A2<$A$2:$A$11)*1)+1 向下復制到單元格B11。 公式釋義：用A2到$A$2:$A$11(用絕對引用$保證公式在向下復制時整個數(shù)據(jù)區(qū)域不發(fā)生變化)中進行比較,當A2<$A$2:$A$11成立時,則返回TRUE;如果A2<$A$2:$A$11不成立就返回FALSE。所以數(shù)組公式=A2<$A$2:$A$11返回一個由TRUE和FALSE構(gòu)成的邏輯數(shù)組。把數(shù)組公式=A2<$A$2:$A$11構(gòu)成的邏輯數(shù)組乘1，得到一個由0和1構(gòu)成的新數(shù)組。SUMPRODUCT再對由0和1構(gòu)成的新數(shù)組求和，表示在數(shù)據(jù)區(qū)域$A$2: