Excel包含文字加總是工作上常常會遇到的需求，本文先介紹SUMIF基本用法，說明如何解決這個問題，同時補齊6套必學公式，包括不等於和排除條件等的設定。

工作上很常用到的函數

會計工作中在報表資料整理好了之後，常常會依照特定條件加總金額，Excel這方面最適合的函數便是SUMIF。依照實務狀況不同，需要不同的設定條件，這一節以業務帳款餘額表作為範例，分享SUMIF函數的進階條件設定，以便靈活應用於各個實務工作場合。

實際案例步驟教學

Step 1 業務帳款餘額表

以部門業務人員開展的應收帳款餘額，主要分類欄位有部門、業務員、銷售客戶、幣別，本節希望以這些分類利用SUMIF函數執行條件加總。

Step 2 基本公式用法

首先是最基本的SUMIF條件加總公式，依照部門加總「=SUMIF(A:A,I2,F:F)」，依照業務員加總：「=SUMIF(B:B,I6,F:F)」，依照幣別加總：「=SUMIF(D:D,I11,F:F)」，A欄、B欄、D欄分別為報表中的部門、業務員、幣別欄位。SUMIF函數第一參數為條件範圍，第二參數為條件值，第三參數為加總範圍，透過本步驟三個公式作為範例，應該不難理解SUMIF函數的用法。這裡特別在原始報表和三組函數公式都有合計金額，皆為「450,000」，簡單驗證公式設計是否無誤。

Step 3 SUMIF不等於

在上個步驟的基本用法基礎上，首先介紹如何以不等於作為條件值：「=SUMIF(A:A,”<>業務一部”,F:F)」，這裡的「”<>業務一部”」表示不等於業務一部的意思，在Excel工作表會以英文雙引號輸入純粹文字，於函數公式中，英文雙引號除了輸入純粹文字外，還可以作為數學計算的條件式，這裡J2公式的計算結果為「770,000」，是加總了非業務一部帳款「320,000」，同時也會加總合計「450,000」，因為A11的合計同樣是非業務一部。為解決原報表合計金額亦納入計算的問題，稍加修改公式：「=SUMIF(A2:A10,”<>業務一部”,F2:F10)」，將範圍限定。這缺失在資料筆數不多的情況下，很容易察覺，然而實務通常資料筆數繁多，所以一定要養成像第二驟那麼勾稽檢查的習慣。

如果已經熟悉「不等於業務一部」的公式設計，「不等於NTD」函數設計應該不成問題，僅僅照樣造句而已。順便補充，SUMIF的加總條件欄的範圍長度，必須和加總金額欄位一致，這裡比較了範圍未限定和範圍限定兩者函數公式寫法，相信會幫助理解SUMIF第一參數和第三參數範圍必須對稱的觀念。

Step 4 排除條件加總

上個步驟有提到英文雙引號可應用於數學計算條件式，在Excel還可以將雙引號數學式利用「&」與函數計算結果合在一起，例如「=SUMIF(F:F,”>=50000″)」中的「”>=50000″」代表以大於等於50000作為加總F欄的條件，「=SUMIF(F:F,”>=”&AVERAGE(F:F))」中的「”>=”&AVERAGE(F:F)」代表以大於等於F欄平均值作為加總F欄的條件，這兩個公式執行的是相同的任務：加總原始報表F欄本幣金額中大於平均值的帳款。

另外上個步驟有遇到原始報表合計金額所造成的小麻煩，此步驟乾脆將「合計本幣金額」和「平均本幣金額」以函數公式在表外計算作為參照：「=SUM(F:F)」、「=SUM(F:F)/(COUNTA(F:F)-1)」。

Step 5 Excel包含文字加總

接下來介紹萬用字元「*」的用法。「*」可以作為替代不限文字長度的模糊查找符號，例如公式「=SUMIF(C:C,”*富廣*”,F:F)」中的「”*富廣*”」，代表任何有包含「富廣」兩個字的內容，所以「富廣」和「富廣A」皆符合條件。公式「=SUMIF(C:C,”*”&I8&”*”,F:F)」等同於「=SUMIF(C:C,”*長源*”,F:F)」，之所以特別設計為「”*”&I8&”*”」，用意是把萬用字元和儲存格參照合併，方便複製公式。這裡將J8儲存格公式向下拖曳延伸到J9，即得到包含「高鴻」的SUMIF加總公式：「=SUMIF(C:C,”*”&I9&”*”,F:F)」。

Step 6 特定文字加總

緊接著介紹萬用字元「?」的用法。「?」是替代一個字元長度的模糊查找符號。例如公式：「=SUMIF(C:C,”??”,F:F)」中的「”??”」，代表範圍內任何兩個字元長度的儲存格皆符合條件，因此計算結果為「130,000」，剛好是第二步驟「業務一部」、「王一」、「NTD」的加總計算金額。公式「=SUMIF(C:C,”??A”,F:F)」中的「”??A”」，代表長度是三個字元且以A結尾作為條件，因此計算結果「220,000」和第二步驟的「幣別」「USD」相同。依此類推，公式「=SUMIF(C:C,”??J”,F:F)」計算結果「100,000」，等同於第二步驟的「幣別」「JPY」的加總金額。透過這三個公式範例，讀者應能掌握萬用字元「?」的技巧。

Step 7 多條件陣列公式

最後再補充較為特殊進階的用法。「=SUM(SUMIF(D:D,{“USD”,”JPY”},F:F))」為陣列公式，其作用等同於將「=SUMIF(D:D, “USD”,F:F)」和「=SUMIF(D:D, “JPY”,F:F)」兩個公式計算結果再以SUM加總，所以金額為220,000+100,000=320,000。在加總條件為兩個以上的情況時，使用陣列公式將會相當簡潔方便，讀者可進一步參考範例檔案中J6和J7的公式設定。

另外實務工作常常會有部份資料為錯誤訊息，例如J11儲存格「#DIV/0!」表示是除數為零的錯誤數學計算，如果直接以「=SUM(F:F)」加總，會跑出相同的「#DIV/0!」錯誤訊息。在此可以簡單利用極大值條件排除非數值儲存格，例如9e307在Excel中表示是9乘以10的307次方、數字9後面有307個0，是一個相當大的數值，所以公式「=SUMIF(F:F,”<9e307″)」意思是加總F欄中數值內容，計算結果即為不計錯誤訊息的合計本幣金額「450,000」。

強大必學的萬用字元

萬用字元「*」和「?」在Excel有普遍性的應用，不但像這節範例可作為函數的條件設定，也可以在篩選或格式化條件等指令中使用。筆者也有遇過公司ERP系統支持以萬用字元設定查詢和下報表條件。可考慮修改為：「再擴大而言，很多其他的電腦操作也會用到萬用字元，不侷限於Excel。」希望透過這一節範例分享，讀者可以熟稔運用這兩個強大的萬用字元，有興趣可以參考贊贊小屋相關文章：

延伸閱讀1（Excel萬用字元）：Sumif用法：如何設計Excel函數公式及進階萬用字元。

延伸閱讀2（VBA萬用字元）：VBA運算子：20個必須掌握的關鍵字及特殊符號。

微軟參考說明：SUMIF 函數。

本篇文章相關的講解影片：

想學習更多Excel技巧嗎？歡迎前往贊贊小屋Excel教學中心。