Excel SUMIF 函數用法教學:判斷條件,計算總和

Excel 的 SUMIF 函數可以依照使用者自訂的判斷條件,計算符合條件的數值總和。

在 Excel 表格中計算數值的加總時,最簡單的方式就是使用 SUM 函數,但如果想要依照某些判斷條件篩選加總的數值,就要改用 SUMIF 函數。以下是 SUMIF 函數的基本用法教學與應用範例。


如果您想要依據各種判斷條件進行個數的統計,請參考 COUNTIFCOUNTIFS 函數的教學

基本 SUMIF 用法

假設我們有一個商品銷售的 Excel 表格,上面有各個銷售員販售各種商品的資料。

Excel 表格資料

如果我們想要快速算出「葵花寶典」這個商品的販售總數,就很適合使用 SUMIF 函數來處理,這裡我們示範兩種操作方式,一種是使用 Excel 工具列上面的「插入函數」工具,另外一種是直接用鍵盤輸入 Excel 儲存格中的公式。

Excel 插入函數工具

Excel 工具列的「插入函數」工具可以讓使用者透過視窗介面來插入各種函數,函數與資料範圍的選擇都可以使用滑鼠操作,這種做法適合初學者或是不習慣寫程式的人使用。
Step 1
在 Excel 中選擇一個要放置計算結果的儲存格(這裡我選擇右邊的 H4 儲存格),接著點選「公式」頁面中的「插入函數」功能。

插入函數

Step 2
在「數學與三角函數」類別中,選擇「SUMIF」函數。

插入函數

Step 3
接著選擇 SUMIF 函數的三個引數。

第一個 Range 引數是指要用來進行條件判斷用的檢查資料,這裡我們要做的事情就是找出商品是「葵花寶典」的資料,所以檢查資料的範圍就要設定為商品那一欄,也就是 C2:C16

第二個 Criteria 引數是判斷條件,這裡我要找尋的商品是「葵花寶典」,所以這一欄就直接填入 葵花寶典

第三個 Sum range 引數則是實際用來計算加總的數值資料範圍,而這裡我們實際要計算的數值就是「葵花寶典」的銷售數量,所以選擇數量那一個欄位,也就是 E2:E16

設定 SUMIF 函數引數

這樣設定好之後,SUMIF 函數就會把 Range 範圍的資料一個接著一個拿來檢查,如果符合 Criteria 所設定的條件的話,就把 Sum range 對應位置的數值加起來,如果不符合條件則跳過該筆資料。

設定好三項引數之後,按下「確定」。
Step 4
這樣在 H4 儲存格中就會出現計算的結果,也就是「葵花寶典」的總銷售數量。

計算結果

直接輸入公式

對於習慣撰寫程式的人,可以在 Excel 儲存格中直接輸入公式,公式用法如下:

=SUMIF(檢查資料,判斷條件,加總資料)

以這裡計算「葵花寶典」總銷售數量的例子來說,就是這樣寫:

=SUMIF(C2:C16,"葵花寶典",E2:E16)

其實上面所介紹的「插入函數」工具就只是產生這一行公式而已,熟悉 SUMIF 的語法之後,其實直接打比較快。

SUMIF 函數語法

多項條件

如果我們想要找出「葵花寶典」與「辟邪劍譜」兩項商品的總銷售數量,可以直接使用兩個 SUMIF 計算個別商品的銷售數量總和,再把他們加起來:

=SUMIF(C2:C16,"葵花寶典",E2:E16)+SUMIF(C2:C16,"辟邪劍譜",E2:E16)

這樣就可以得到兩種商品的銷售數量總和,這種方式是最直覺的做法。

多項條件計算加總

另外一種方式是使用多項判斷條件,同時計算兩項商品的個別銷售數量,再使用 SUM 函數把兩個總數加起來:

=SUM(SUMIF(C2:C16,{"葵花寶典","辟邪劍譜"},E2:E16))

這種方式也可以得到同樣的結果,而公式寫起來也比較精簡。

多項條件計算加總

日期範圍條件

SUMIF 也可以依照日期來篩選加總的資料。

如果我們想要計算 2017 年 4 月份之後,所有商品的銷售總量,可以這樣寫:

=SUMIF(A2:A16,">=2017/4/1",E2:E16)

這裡我們將檢查資料的範圍設為日期那一欄,而判斷條件則設定為 ">=2017/4/1",代表 2017 年 4 月 1 日當天或是之後。

依照日期條件加總

這樣就可以計算出 2017 年 4 月份開始的所有商品銷售總量。

依照日期條件加總

日期區間

如果想要計算介於兩個日期中間的資料,可以使用兩個 SUMIF 來處理。

若我們只想要計算 2017 年 4 月以及 5 月的銷售總量,可以先用 SUMIF 計算 4 月份開始的銷售總量,再扣掉 6 月份之後的總量:

=SUMIF(A2:A16,">=2017/4/1",E2:E16)-SUMIF(A2:A16,">=2017/6/1",E2:E16)

這樣就得到我們想要的結果了。

依照日期區間加總

另一個方式是改用 SUMIFS 這個可以接受多個判斷條件的 Excel 函數,其第一個參數是加總資料,後方可接任一個檢查資料與判斷條件的組合配對:

=SUMIFS(E2:E16,A2:A16,">=2017/4/1",A2:A16,"<2017/6/1")

這樣就可以得到 4 月以及 5 月的銷售總量。

依照日期區間加總

數值條件

如果要依照數值的判斷條件來加總,就直接選擇數值的檢查資料,配上數值的判斷條件即可。

這是計算單價在 100 元以上的商品銷售總量:

=SUMIF(D2:D16,">=100",E2:E16)

依照數值條件加總

萬用字元條件

如果文字的判斷條件不是很明確時,可以配合萬用字元(星號 * 或問號 ?)來匹配比較模糊的文字。

假設我們想要找出所有姓「岳」的銷售員(「岳靈珊」、「岳不群」)所有的銷售總量,就可以這樣寫:

=SUMIF(B2:B16,"岳*",E2:E16)

星號(*)代表任意的文字(不限長度),這樣 SUMIF 就會檢查所有的銷售員資料,把所有以「岳」開頭的銷售員都抓出來,計算其對應的銷售數量總和。

另外一個常用的萬用字元是問號(?),其代表單一個任意的文字,所以如果我們想要比對「岳」開頭、而且後面街上兩個任意文字的資料,就可以這樣寫:

=SUMIF(B2:B16,"岳??",E2:E16)

這樣同樣可以找出「岳靈珊」與「岳不群」的資料。

其他範例

如果檢查用的資料與加總用的資料相同,則可將 SUMIF 的第三個加總資料引數省略,這樣的話 SUMIF 就會使用第一個引數的資料進行檢查與加總。

若要把所有總價在 300 以上的資料加起來,可以這樣寫:

=SUMIF(F2:F16,">=300")

這樣就得到所有總價在 300 以上加總。

加總結果

參考資料:簡書

Windows

7 留言

  1. 陳小姐

    請問如何統計每本書的銷售數量?
    感謝教學~

  2. Ryan

    可以用儲存格顏色做判斷條件嗎?

  3. Qoo

    感謝大大無私的分享
    我使用第2個方法計算”多項條件計算加總”
    =SUM(SUMIF(C2:C16,{“葵花寶典”,”辟邪劍譜”},E2:E16))
    不過取得的值,仍只有第一項”葵花寶典”的加總數字
    是不是有那邊公式錯了呢

  4. Steven

    請問有辦法把除了葵花寶典之外的東西加起來嗎?
    其他的寶典太多種,30~40種沒辦法一個一個打的情況下

  5. WANG

    請問
    如果這一格有任何數字就算1件,這樣的件數總和該如何寫?

Comments are Closed