在EXCEL中，SUMIFS函數是"多條件求和"函數，但是我們工作需要完成多條件求差，EXCEL里是沒有"多條件求差值"函數的，所以我們可以逆向利用多條件求和函數SUMIFS來完成"多條件求差值"。

▌先介紹下SUMIFS函數的參數和基礎案例：

?參數：SUMIFS(提取求和的區域，包含條件值1的條件區域，條件值1，……）最多可以有127個條件區域和127個條件值。

① 基礎案例一：對每個產品分地區求和，如圖1

圖1

② 因為這兩個表的首行"地區"順序是一致的，都是從"一區"到"五區"，所以我們只要在J5單元格輸入公式，向右填充，向下填充就可以批量計算完每個產品的合計值。

圖2

③ J5單元格輸入公式=SUMIFS(C$2:C$11,$B$2:$B$11,$I5)，然后選中光標，向右，向下拖動就可以了。

• 參數1：要提取求和的區域，"行絕對引用"是為了保證數據區域是從第2行開始到第11行結束，"列相對引用"是為了保證向右拖動時，數據區域會從第C列變成第D列。

• 參數2：包含條件值"A產品"的條件區域，"絕對引用"是條件區域鎖定，不會發生偏移。

• 參數3：表示要根據條件值"A產品"求合計，向下拖動就會變成C產品、D產品、B產品。

▌介紹完用SUMIFS"多條件求和"的用法，現在講解下怎么利用SUMIFS函數來完成"多條件求差值"，完成求減法功能。

圖3

?如圖3、求差值案例二：求每個產品在每個區還剩多少庫存？(總入庫-總出庫)

思路分析："A產品"在"一區"的庫存 = "一區A產品的入庫數 - 一區A產品的出庫數"。

1. K4單元格輸入=SUM(SUMIFS($D$2:$D$15,$B$2:$B$15,$J4,$C$2:$C$15,{"入庫","出庫"})*{1,-1})，因為是數組計算，所以輸入完公式后要按CTRL+Shift+回車才可以。
2. SUMIFS($D$2:$D$15,$B$2:$B$15,$J4,$C$2:$C$15,{"入庫","出庫"})得到結果{1184,738}，因為SUMIFS無法對數組求和，所以要在最前面加一個數組求和函數SUMPRODUCT。
3. =SUMPRODUCT({1184,738}*{1,-1})變成=SUMPRODUCT(1184*1+738*(-1)),返回最終結果446。
4. 然后向右拖動，向下拖動就行。

思路解析圖片版：如圖4

圖4

思路拓展：如果是“入庫總數”+“出庫總數”，就省略不寫{1，-1}，因為SUMPRODUCT本身就是數組求和計算。

?如圖5、案例三：按日期多條件求和或多條件求差值

圖5

思路分析：按“一月份”、“二月份”或“三月份”等月份的條件求和，首先插入一列輔助列，用MONTH函數提取月份數，再根據“月份數”條件求和。

思路解析圖片版：如圖6

圖6

如果想求差值，3月份-1月份是多少？

輸入公式=SUMPRODUCT(SUMIFS(C$2:C$15,$H$2:$H$15,{3,1})*{1,-1})，

按CTRL+Shift+回車，得出結果為-2178。3月少，1月多，所以結果為負數。

?如圖7、案例四：參數添加通配符*，數組模糊查找條件求和

如圖7

在J2單元格輸入=SUMPRODUCT(SUMIFS(C$2:C$15,$B$2:$B$15,{"*A*","*b*"}))，按CTRL+Shift+回車，完成數組求和。字母B和b是一樣的，不區分大小寫。

?如圖8、案例五：合并單元格怎么正確多條件求和？

圖8

在A列有合并單元格下，直接在H3單元格輸入公式=SUMIFS(B$2:B$8,$A$2:$A$8,$G3)，得出的結果"800"是錯誤的。

思路分析：我們需要對A列的合并單元格進行細節處理，才可以用SUMIFS條件求和。

1. 選中A2:A8區域——點“開始”選項卡的"格式刷",在隨便一個區域點一下，我們選中O列O2單元格，這時O2:O8就會變出同樣的合并單元格。
2. 選中A2:A8區域——點“開始”選項卡的“合并后居中”，就取消了合并單元格——按F5，定位條件，空值——直接輸入公式=A2，按CTRL+回車，就填充好了。
3. 選中O2:O8區域——點“開始”選項卡的"格式刷"，在A2單元格點一下，就會重新出現合并單元格。
4. 這時再用公式計算，在H3單元格輸入公式=SUMIFS(B$2:B$8,$A$2:$A$8,$G3)，得出結果1856是正確的。

如圖9：合并單元格處理演示

圖9：合并單元格細節處理

如圖10：SUMIFS多條件求和演示

圖10：多條件求和操作演示

?如圖11、案例六：“求和區域”順序不一致，怎么自動變換求和區域？

圖11

思路分析：我們通過“公式”選項卡下的“定義名稱”，再配合INDIRECT函數來實現動圖引用。

1. 選中B1:E8區域——點“公式”選項卡下的“根據所選內容創建”，選首行，確定。
2. 這時“名稱管理器”里就有根據“一區”、“二區”、“三區”、“四區”建立的名稱。
3. 在H3單元格輸入=SUMIFS(INDIRECT(H$2),$A$2:$A$8,$G3)，完成。
4. INDIRECT(H$2)意思是引用H2單元格"二區"地址的內容，因為我們”定義了名稱",所以會引用"二區"對應的數據區域。（關于INDIRECT函數的使用可以看我的前面文章）

如圖12：“定義名稱”動態演示

圖12

如圖13：嵌套INDIRECT函數，引用“定義名稱”的內容，完成動態引用求和區域，操作演示

圖13

以上就是EXCEL的SUMIFS多條件求和函數的相加，相減及數組加減的用法。