大家習(xí)慣性的認(rèn)為SUMIF就是1列條件區(qū)域1列求和區(qū)域。實(shí)際上并非如此，SUMIF條件求和的規(guī)則或原理：可以有多列條件區(qū)域，它按條件區(qū)域的大小與求和區(qū)域進(jìn)行成對(duì)的條件求和。利用這點(diǎn)可以很簡(jiǎn)單地搞定多表匯多表查找、并列表匯總、錯(cuò)位求和等等。

在《按條件進(jìn)行多列求和》的教程中，小窩曾提到SUMIF的奇怪規(guī)則，由下面的一個(gè)故意為難人的要求引起。

小窩的解決方法就是增加兩列姓名,讓條件區(qū)域和求和區(qū)域大小一致：

以上用到了SUMIF鮮為人知的運(yùn)算規(guī)則：

以條件區(qū)域大小為基礎(chǔ)，將條件列（或行）和求和列（或行）按相同列序（或行序）成對(duì)，然后累加每對(duì)中符合條件的數(shù)據(jù)。

啥意思？下方的示意圖有利于大家理解。

小窩把條件區(qū)域各列中“王靜”的排序調(diào)整一下，大家看得更明白：

首先在第1列條件中查找“王靜”，返回第1列求和區(qū)域中的“142”；接著在第2列條件中查找，返回第2列求和區(qū)域中的“109”；再在第3列條件中查找，返回第3列求和區(qū)域中的“60”；最后三者相加就等于311。

由于是以條件區(qū)域的大小為基礎(chǔ)進(jìn)行成對(duì)條件求和，所以求和區(qū)域可以簡(jiǎn)寫，只引用區(qū)域中第一個(gè)單元格，SUMIF在實(shí)際運(yùn)算中會(huì)自動(dòng)補(bǔ)齊，讓求和區(qū)域與條件區(qū)域大小相等。

了解了SUMIF按照條件區(qū)域大小成對(duì)進(jìn)行條件求和的規(guī)則，那神秘的錯(cuò)位求和、多表查找就很簡(jiǎn)單了。

1.錯(cuò)位求和

所謂的錯(cuò)位求和，就是指條件區(qū)域和求和區(qū)域有重疊，或者位置有錯(cuò)行或者錯(cuò)列。

1）多表匯總、并列表匯總——條件區(qū)域和求和區(qū)域重疊

譬如求下方王文、劉新的總分，可以認(rèn)為是按條件匯總英語、語文、數(shù)學(xué)三個(gè)并列表中的數(shù)據(jù)。

簡(jiǎn)單，公式=SUMIF($A$21:$E$26,H20,$B$21:$F$26)，向下填充即可。

說明：

把姓名到姓名列A21:E26作為條件區(qū)域，分?jǐn)?shù)到分?jǐn)?shù)列B21:F26作為求和區(qū)域，然后進(jìn)行成對(duì)條件求和，實(shí)質(zhì)如下圖。

在第1列條件中查“王文”，返回第1列求和區(qū)域中的69；在第2列條件中查找，沒有符合條件的，返回0……以此類推，完成5對(duì)條件求和并累加69+0+99+0+61=229。

2）求所有列最后一個(gè)數(shù)據(jù)的和

譬如求下方5種產(chǎn)品最后報(bào)價(jià)之和。

也很簡(jiǎn)單，公式=SUMIF(B41:F47,"",B40:F46)

說明：

將第二次報(bào)價(jià)到最后一次報(bào)價(jià)下一行B41:F47作為條件區(qū)域，將第一次報(bào)價(jià)到最后一次報(bào)價(jià)B40:F46作為求和區(qū)域，條件是空。此處既有重疊——條件區(qū)域和求和區(qū)域存在重疊，又有位置錯(cuò)位——條件區(qū)域位與求和區(qū)域錯(cuò)開了一行（但是大小一致）。

第1列條件中等于空的，對(duì)應(yīng)第1列求和區(qū)域中的90和0（空）；第2列條件中等于空的，對(duì)應(yīng)第2列求和區(qū)域中的94……以此類推得到各列最后一次報(bào)價(jià)并累加。

如果表格是橫向的，也是一樣的用SUMIF求和搞定。

2.多表查找并列表查找

譬如從下方并列的多個(gè)表中查找林菲的成績(jī)和學(xué)號(hào)。小窩曾分享過用INDIRECT進(jìn)行多表格查找，但太復(fù)雜了。今天借助SUMIF來查找。

因?yàn)椴⒉恢懒址莆挥谀且涣?，所以不管?span>VLOOKUP，還是XLOOKUP，都不好使。

但用SUMIF很簡(jiǎn)單就搞定。

公式=SUMIF($A$72:$D$80,$H$72,B72:E80)，然后向右填充即可。

說明：

由于沒有重名，成績(jī)和學(xué)號(hào)都是數(shù)字，所以用SUMIF求和可以完美的搞定多表查找。

把姓名到姓名區(qū)域$A$72:$D$80作為條件區(qū)域，把成績(jī)到成績(jī)區(qū)域B72:E80作為求和區(qū)域，就可以獲得成績(jī)。

把姓名到姓名區(qū)域$A$72:$D$80作為條件區(qū)域，把學(xué)號(hào)到學(xué)號(hào)區(qū)域C72:F80作為求和區(qū)域，就可以獲得學(xué)號(hào)。

本文配套的練習(xí)課件請(qǐng)?zhí)砑涌头⑿?span>buluowojiaoyu索取。

做Excel高手，快速提升工作效率，部落窩教育Excel精品好課任你選擇！

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

相關(guān)推薦：

多區(qū)域查找就用INDIRECT

含單位和通配符的數(shù)字求和

按條件進(jìn)行多列求和

為何根據(jù)顏色查找單元格查找不到？

版權(quán)申明：

本文作者小窩；部落窩教育享有稿件專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。