SUMPRODUCT多條件求和.doc

Excel多條件求和日常工作中，我們經(jīng)常要用到多條件求和，方法有多種，第一類：使用基本功能來實現(xiàn)。主要有：篩選、分類匯總、數(shù)據(jù)透視表、多條件求和向導；第二類：使用公式來實現(xiàn)方法。主要有：使用SUM函數(shù)編寫的數(shù)組公式、聯(lián)用SUMIF和輔助列（將多條件變?yōu)閱螚l件）、使用SUMPRODUCT函數(shù)、使用SUMIFS函數(shù)(限于Excel2007及以上的版本)，方法千差萬別、效果各有千秋。本人更喜歡用SUMPRODUCT函數(shù)。由于Excel幫助對SUMPRODUCT函數(shù)的解釋太簡短了，與SUMPRODUCT函數(shù)的作用相比實在不匹配，為了更好地掌握該函數(shù)，特將其整理如下。一、 基本用法在給定的幾組數(shù)組中，將數(shù)組間對應的元素相乘，并返回乘積之和。語法：SUMPRODUCT(array1,array2,array3, .)Array1, array2, array3, . 為 2 到 30 個數(shù)組，其相應元素需要進行相乘并求和。公式：=SUMPRODUCT(A2:B4, C2:D4)ABCD1Array1Array1Array2Array2234273866741953公式解釋：兩個數(shù)組的所有元素對應相乘，然后把乘積相加，即 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3。計算結果為156二、 擴展用法1、 使用SUMPRODUCT進行多條件計數(shù)語法：SUMPRODUCT(條件1)*(條件2)*(條件3)* (條件n)作用：統(tǒng)計同時滿足條件1、條件2到條件n的記錄的個數(shù)。實例：=SUMPRODUCT(A2:A10=男)*(B2:B10=中級職稱)公式解釋：統(tǒng)計性別為男性且職稱為中級職稱的職工的人數(shù)2、 使用SUMPRODUCT進行多條件求和語法：SUMPRODUCT(條件1)*(條件2)* (條件3) *(條件n)*某區(qū)域)作用：匯總同時滿足條件1、條件2到條件n的記錄指定區(qū)域的匯總金額。實例：=SUMPRODUCT(A2:A10=男)*(B2:B10=中級職稱)*C2:C10)公式解釋：統(tǒng)計性別為男性且職稱為中級職稱的職工的工資總和（假設C列為工資）三、 注意事項1、數(shù)組參數(shù)必須具有相同的維數(shù)，否則，函數(shù) SUMPRODUCT 將返回錯誤值 #VALUE!。 2、SUMPRODUCT函數(shù)將非數(shù)值型的數(shù)組元素作為 0 處理。3、在SUMPRODUCT中，2003及以下版本不支持整列（行）引用，必須指明范圍，不可在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=中級職稱)*(LEFT(D2:D10,1)=龍)*C2:C10)=SUMPRODUCT(A2:A10=男)*(B2:B10=中級職稱)*(ISNUMBER(FIND(龍逸凡,D2:D10)*C2:C10)注：以上公式假設D列為職工姓名。ISNUMBER(FIND()、ISNUMBER(SEARCH()作用是實現(xiàn)“*”的通配功能，只是前者區(qū)分大小寫，后者不區(qū)分大小寫。5、SUMPRODUCT函數(shù)多條件求和時使用“，”和“*”的區(qū)別：當擬求和的區(qū)域中無文本時兩者無區(qū)別，當有文本時，使用“*”時會出錯，返回錯誤值 #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ù)值時，兩者計算結果一樣，當C2:C10中有文本時公式1會返回錯誤值 #VALUE!，而公式2會返回忽略文本以后的結果。四、 網(wǎng)友們的精彩實例1、求指定區(qū)域的奇數(shù)列的數(shù)值之和=SUMPRODUCT(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:P