文 前 推 薦
不能用SUMIF
編按：
大家習慣性的認為SUMIF就是1列條件區域1列求和區域。實際上並非如此，SUMIF條件求和的規則或原理：可以有多列條件區域，它按條件區域的大小與求和區域進行成對的條件求和。利用這點可以很簡單地搞定多表匯多表尋找、並列表匯總、錯位求和等等。
在【按條件進行多列求和】的教程中，小窩曾提到SUMIF的奇怪規則，由下面的一個故意為難人的要求引起。
小窩的解決方法就是增加兩列姓名,讓條件區域和求和區域大小一致：
以上用到了 SUMIF鮮為人知的運算規則：
以條件區域大小為基礎，將條件列（或行）和求和列（或行）按相同列序（或行序）成對，然後累加每對中符合條件的數據。
啥意思？下方的示意圖有利於大家理解。
小窩把條件區域各列中「王靜」的排序調整一下，大家看得更明白：
首先在第1列條件中尋找「王靜」，返回第1列求和區域中的「142」；接著在第2列條件中尋找，返回第2列求和區域中的「109」；再在第3列條件中尋找，返回第3列求和區域中的「60」；最後三者相加就等於311。
由於是 以條件區域的大小為基礎進行成對條件求和 ，所以求和區域可以簡寫，只參照區域中第一個單元格，SUMIF在實際運算中會自動補齊，讓求和區域與條件區域大小相等。
了解了SUMIF按照條件區域大小成對進行條件求和的規則，那神秘的錯位求和、多表尋找就很簡單了。
1. 錯位求和
所謂的錯位求和，就是指條件區域和求和區域有重疊，或者位置有錯行或者錯列。
1）多表匯總、並列表匯總——條件區域和求和區域重疊
譬如求下方王文、劉新的總分，可以認為是按條件匯總英語、語文、數學三個並列表中的數據。
簡單，公式=SUMIF($A$21:$E$26,H20,$B$21:$F$26)，向下填充即可。
說明：
把姓名到姓名列A21:E26作為條件區域，分數到分數列B21:F26作為求和區域，然後進行成對條件求和，實質如下圖。
在第1列條件中查「王文」，返回第1列求和區域中的69；在第2列條件中尋找，沒有符合條件的，返回0……以此類推，完成5對條件求和並累加69+0+99+0+61=229。
2）求所有列最後一個數據的和
譬如求下方5種產品最後報價之和。
也很簡單，公式=SUMIF(B41:F47,"",B40:F46)
說明：
將第二次報價到最後一次報價下一行B41:F47作為條件區域，將第一次報價到最後一次報價B40:F46作為求和區域，條件是空。此處既有重疊——條件區域和求和區域存在重疊，又有位置錯位——條件區域位與求和區域錯開了一行（但是大小一致）。
第1列條件中等於空的，對應第1列求和區域中的90和0（空）；第2列條件中等於空的，對應第2列求和區域中的94……以此類推得到各列最後一次報價並累加。
如果表格是橫向的，也是一樣的用SUMIF求和搞定。
2. 多表尋找並列表尋找
譬如從下方並列的多個表中尋找林菲的成績和學號。小窩曾分享過用INDIRECT進行多表格尋找，但太復雜了。今天借助SUMIF來尋找。
因為並不知道林菲位於那一列，所以不管是VLOOKUP，還是XLOOKUP，都不好使。
但用SUMIF很簡單就搞定。
公式=SUMIF($A$72:$D$80,$H$72,B72:E80)，然後向右填充即可。
說明：
由於沒有重名，成績和學號都是數位，所以用SUMIF求和可以完美的搞定多表尋找。
把姓名到姓名區域$A$72:$D$80作為條件區域，把成績到成績區域B72:E80作為求和區域，就可以獲得成績。
把姓名到姓名區域$A$72:$D$80作為條件區域，把學號到學號區域C72:F80作為求和區域，就可以獲得學號。
課件下載方式
掃碼入群，下載視訊配套的Excel課件練習。
最後，歡迎加入Excel函式訓練營，學習68個函式、練習課件、輔導答疑。
