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

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

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

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

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

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

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

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

由于是以條件區(qū)域的大小為基礎(chǔ)進行成對條件求和，所以求和區(qū)域可以簡寫，只引用區(qū)域中第一個單元格，SUMIF在實際運算中會自動補齊，讓求和區(qū)域與條件區(qū)域大小相等。

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

1.錯位求和

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

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

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

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

說明：

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

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

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

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

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

說明：

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

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

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

2.多表查找并列表查找

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

因為并不知道林菲位于那一列，所以不管是VLOOKUP，還是XLOOKUP，都不好使。

但用SUMIF很簡單就搞定。

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

說明：

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

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

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

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

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

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

相關(guān)推薦：

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

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

按條件進行多列求和

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

版權(quán)申明：

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