1、多條件計(jì)算人數(shù)=SUMPRODUCT((I241:I250="是")*1)
公式說明：SUMPRODUCT函數(shù)的功能是在給定的幾組數(shù)組中，將數(shù)組間對(duì)應(yīng)的參數(shù)相乘，并返回乘積之和。如果只有一組，則直接返
回該數(shù)組之和。本例SUMPRODUCT函數(shù)就只有一個(gè)數(shù)組參數(shù)I241:I250，返回其中等于"是"的個(gè)數(shù)。
使用注意：1、SUMPRODUCT函數(shù)的參數(shù)可以是1至255個(gè)數(shù)組參數(shù)。如果只有一個(gè)參數(shù)，則返回該數(shù)組參數(shù)之和，如果有多組參數(shù)，則
將所有數(shù)組對(duì)應(yīng)的值相乘再將乘積匯總。所以SUMPRODUCT函數(shù)其實(shí)也就是多條件求和。2、SUMPRODUCT函數(shù)如果有多個(gè)數(shù)組參數(shù)，各
數(shù)組的大小必須相等，例如第一參數(shù)是I241:I250，第二參數(shù)就不能是I241:I249等。3、如果SUMPRODUCT函數(shù)的參數(shù)中有非數(shù)值型數(shù)據(jù)，函數(shù)在計(jì)算時(shí)將非數(shù)值型的數(shù)組元素作為0處理。4、SUMPRODUCT函數(shù)的參數(shù)中如果有邏輯值TRUE或者FALSE，也都當(dāng)做0處理。通常使用"*1"或者"--"將之轉(zhuǎn)換成數(shù)值，然后再計(jì)算。
2、求指定年齡、性別人數(shù)=SUMPRODUCT((H252:H260="男")*1,(I252:I260>25)*1)
公式說明：本例公式中SUMPRODUCT函數(shù)有兩個(gè)數(shù)組參數(shù)，都是需要進(jìn)行運(yùn)算的比較表達(dá)式。表達(dá)式運(yùn)算結(jié)果是包含TRUE和FALSE的數(shù)
組，為了讓數(shù)組中的TRUE能轉(zhuǎn)換為1，F(xiàn)ALSE能轉(zhuǎn)換成0參與運(yùn)算，分別對(duì)兩個(gè)數(shù)組"*1"。最后將兩個(gè)數(shù)組中的對(duì)應(yīng)元素分別相乘，并將乘積的結(jié)果累加，得到符合條件的人數(shù)總和。
使用注意：1、SUMPRODUCT函數(shù)進(jìn)行的運(yùn)算是數(shù)組運(yùn)算，在輸入公式時(shí)不需要利用【Ctrl+Shift+Enter】組合鍵輸入數(shù)組，但是它的
運(yùn)算結(jié)果完全等同于數(shù)組公式。2、對(duì)于本例的需求，也可以用SUM函數(shù)的數(shù)組公式來完成:{=SUM((H252:H260="男")*(I252:I260>25))}。用SUM函數(shù)的數(shù)組形式替換SUMPRODUCT函數(shù)的普通公式，優(yōu)點(diǎn)是公式更短，缺點(diǎn)是每次編輯后必須以【Ctrl
+Shift+Enter】組合鍵結(jié)束，如果無意中雙擊了單元格再按【Enter】鍵結(jié)束，那么公式將產(chǎn)生錯(cuò)誤的運(yùn)算結(jié)果。3、本例的公式也可以修改為一個(gè)參數(shù)，使公式進(jìn)行簡化:=SUMPRODUCT((H252:H260="男")*(I252:I260>25))。這種形式對(duì)兩個(gè)包含邏輯值的數(shù)組相乘，本身就將邏輯值轉(zhuǎn)換為數(shù)值了，所以不再需要"*1"。
3、匯總一班人員獲獎(jiǎng)次數(shù)=SUMPRODUCT((H265:H274="一班")*I265:I274)
公式說明：本公式中SUMPRODUCT函數(shù)有兩個(gè)數(shù)組參數(shù):"H265:H274="一班""和"I265:I274"。根據(jù)本函數(shù)的特點(diǎn)，可以將兩個(gè)數(shù)組通
過乘號(hào)連接成一個(gè)參數(shù)，從而不管兩個(gè)數(shù)組由數(shù)值組成還是由邏輯值組成，都可以不需要轉(zhuǎn)換而直接得到運(yùn)算結(jié)果。
使用注意：1、本例中也可以將兩個(gè)數(shù)組分成兩個(gè)參數(shù)，但第一參數(shù)需要利用"*1"或者其他方式將邏輯值轉(zhuǎn)換成數(shù)值:
=SUMPRODUCT((H265:H274="一班")*1,I265:I274)
=SUMPRODUCT(N(H265:H274="一班"),I265:I274)
=SUMPRODUCT(--(H265:H274="一班"),I265:I274)
2、如果用SUM函數(shù)來運(yùn)算，必須以數(shù)組形式錄入公式:
{=SUM((H265:H274="一班")*I265:I274)}
{=SUM(IF(H265:H274="一班",I265:I274))}
4、匯總一車間男性參保人數(shù)=SUMPRODUCT((G276:G284&H276:H284&I276:I284="一車間男是")*1)
公式說明：本公式中將G列、H列、I列的數(shù)據(jù)通過文本連接符"&"(等同函數(shù)CONCATENATE)連接，然后與設(shè)定的三個(gè)條件"一車間男是"
進(jìn)行比較，若相同就得到一個(gè)邏輯值TRUE，再用"*1"將邏輯值轉(zhuǎn)換成數(shù)值，最后用SUMPRODUCT函數(shù)匯總數(shù)據(jù)。
使用注意：1、本例中公式思路只適用于所有條件中不存在">"、"<"、"<>"、"<="、">="運(yùn)算符的條件，直接將所有數(shù)據(jù)串連起來，也將所有條件按同樣順序串連起來進(jìn)行比較即可。比較的結(jié)果是一個(gè)由邏輯值組成的數(shù)組。2、本例公式也可以改用其他兩種方式:=SUMPRODUCT((G276:G284="一車間")*(H276:H284="男")*(I276:I284="是"))和=SUMPRODUCT((G276:G284="一車間")*1,(H276:H284="男")*1,(I276:I284="是")*1)。3、如果計(jì)算二車間和三車間女性參保人數(shù)，可以采用以下公式:=SUMPRODUCT
((G276:G284<>"一車間")*(H276:H284&I276:I284="女是"))。本公式對(duì)車間名稱使用不等于號(hào)來排除一車間，相比羅列兩個(gè)車間名
稱會(huì)簡潔一些。
5、匯總所有車間人員工資=SUMPRODUCT(--NOT(ISERROR(FIND("車間",G286:G294))),I286:I294)
公式說明：鑒于SUMPRODUCT函數(shù)的參數(shù)不支持通配符，故本公式首先利用FIND函數(shù)在G286:G294區(qū)域中查找"車間"二字，如果找到則
運(yùn)算結(jié)果為一個(gè)數(shù)字，表示"車間"在該單元格中的出現(xiàn)位數(shù)；如果找不到將產(chǎn)生一個(gè)錯(cuò)誤值。然后利用NOT(ISERROR())函數(shù)嵌套來判斷哪些單元格中包括"車間"二字，得到一個(gè)由TRUE和FALSE組成的數(shù)組，再用"--"將這組邏輯值轉(zhuǎn)換成數(shù)值，最后與I286:I294區(qū)域?qū)?yīng)的數(shù)據(jù)相乘并匯總。
使用注意：1、在不支持通配符的所有函數(shù)中，都可以利用NOT(ISERROR(FIND()))的嵌套組合來實(shí)現(xiàn)通配符類似的功能。在本例中相
當(dāng)于"*車間*"，如果部門名稱是"*車間"形式則可以改用以下公式:=SUMPRODUCT(--(RIGHT(G286:G294,2)="車間"),I286:I294)。2、根據(jù)本例的特點(diǎn)，也可以用ISNUMBER函數(shù)來代替NOT(ISERROR())組合，公式如下:=SUMPRODUCT(--(ISUMBER(FIND("車間",G286:G294))),I286:I294)
6、匯總業(yè)務(wù)員業(yè)績=SUMPRODUCT((H296:H305={"江西","廣東"})*(I296:I305="男")*J296:J305)
公式說明：SUMPRODUCT函數(shù)的參數(shù)支持二維數(shù)組，這使它不僅可以匯總同時(shí)滿足多個(gè)條件的數(shù)據(jù)，還可以不借助其他函數(shù)的嵌套就
可以達(dá)到在多條件中符合條件之一，即求和的需要。在本公式中，參數(shù)"{"江西","廣東"}"可以使SUMPRODUCT函數(shù)統(tǒng)計(jì)兩個(gè)省區(qū)的數(shù)
據(jù)，這比SUMIF函數(shù)需要外套SUM函數(shù)簡單些。
使用注意：1、本例公式也可以不使用數(shù)組，改用"+"連接兩個(gè)條件，公式如下:=SUMPRODUCT(((H296:H305="江西")+(H296:H305="廣
東"))*(I296:I305="男")*J296:J305)。2、公式中“+”連接的條件表示滿足任意條件就求和，而“*”連接的條件則表示同時(shí)滿足所有條件。
聯(lián)系客服
