【EXCEL教學】5分鐘一次搞懂 SUMIF & SUMIFS用法

SUMIF 與 SUMIFS 是 EXCEL 中30個必學函數之一,用於根據自訂條件對範圍內的數值進行加總,在處理數據時可以節省大量時間。

本文將會用 5 分鐘的時間,教你 SUMIF 與 SUMIFS 函數的用法、彼此的差別、進階用法以及常見錯誤等等。

因此,如果你是第一次接觸 SUMIF 或 SUMIFS 函數,建議可以把用法徹底搞懂,就不用遇到一次就頭痛一次。

那麼我們就開始吧!

SUMIF & SUMIFS 使用時機

兩個函數都可以數出範圍中,符合特定條件的「數值加總」。不過差別是 SUMIF 只能輸入一組條件,SUMIFS 可以輸入很多組條件

以下是 SUMIF & SUMIFS 的使用時機:

  • 符合單一條件:用 SUMIF
  • 符合 A條件「且」B條件:用 SUMIFS
  • 符合 A條件「或」B條件:用 SUMIF+SUM(請看進階用法

SUMIF 函數用法

SUMIFS 函數用來根據「單個條件」對範圍內的數值進行加總。

以下是 SUMIF 的函數說明:

SUMIF ( range , criteria , [sum_range] )

引數名稱解釋
range需要檢查條件的範圍
criteria自訂條件
[sum_range]要加總的範圍

設定好範圍與條件後,SUMIF 會先將「檢查條件的範圍」根據自訂條件進行篩選,並將符合條件的對應數據進行加總。

接下來,我們就來看看 SUMIF 函數的用法與範例。


SUMIF 加總符合單一條件的值

如果想要知道今天在「正餐」上花了多少錢,可以利用 SUMIF 函數挑選出類型符合「正餐」的花費進行加總:

SUMIF ( B2:B7 , “正餐” , C2:C7 )

引數名稱解釋範例
range需要檢查條件的範圍B2:B7
criteria自訂條件“正餐”
[sum_range]要加總的範圍C2:C7

而如果「需要檢查條件的範圍」本身就是「要加總的範圍」,那麼「要加總的範圍」可以省略不寫。

舉個例子,如果想要知道今天花費金額超過 $100 的加總,SUMIF 可以這樣寫:

SUMIF ( C2:C7 , “>100” )

引數名稱解釋範例
range需要檢查條件的範圍C2:C7
criteria自訂條件“>100”
[sum_range]要加總的範圍C2:C7(也是需要檢查條件的範圍,因此這個引數可以不用填)

非常反直覺的是:自訂條件的運算,要加上雙引號「”>100″」。只打「>100」的話 EXCEL 是不會理你的!


SUMIF 自訂條件參照儲存格

如果要用「參照儲存格」的方式,SUMIF 就需要配合「&」,並且放在雙引號外面

=SUMIF ( C2:C7 , “>”&D2 )


SUMIF 加總符合「日期」條件的值

在 EXCEL 的眼中,日期其實就是不斷累加的「數字」,例如 2024/6/30 是「45473」,2024/7/1 是「45474」。

因此,若要計算日期小於 2024/7/1 的加總,直接設定條件即可:

=SUMIF ( A2:A7 , “>”&C9 , B2:B7 )


SUMIF 常用的自訂條件

最後,補充一些常用的自訂條件

使用情境(與數字有關)自訂條件(記得加上雙引號)
加總C欄大於100的值=SUMIF ( C:C, “>100”,… )
加總C欄大於等於100的值=SUMIF ( C:C, “>=100”,… )
加總C欄小於100的值=SUMIF ( C:C, “<100”,… )
加總C欄小於等於100的值=SUMIF ( C:C, “<=100”,… )
加總C欄等於100的值=SUMIF ( C:C, “=100”,… )
加總C欄不等於100的值=SUMIF ( C:C, “<>100”,… ),EXCEL 中的「<>」代表「≠」
使用情境(與文字有關)自訂條件(記得加上雙引號)
加總C欄是空的儲存格的值=SUMIF ( C:C, “”,… )
加總C欄是非空的儲存格的值=SUMIF ( C:C, “<>”,… )
加總C欄等於「正餐」的值=SUMIF ( C:C, “正餐”,… )
加總C欄不等於「正餐」的值=SUMIF ( C:C, “<>正餐”,… )

SUMIF 的用法就到這裡囉!接下來是 SUMIFS 的教學!


SUMIFS 函數用法

SUMIFS 函數用來根據「多個條件」對範圍內的數值進行加總。

以下是 SUMIFS 的函數說明:

SUMIFS ( sum_range , range1 , criteria1 , range2 , criteria2 )

引數名稱解釋
sum_range要加總的範圍
range1需要檢查條件的範圍1
criteria1自訂條件1
range2需要檢查條件的範圍2
criteria2自訂條件2

其實就只是需要先輸入「要加總的範圍」而已,跟 SUMIF 的輸入順序相反。

接著,我們再來看看 SUMIFS 函數的用法與範例。


SUMIFS 加總符合多個條件的值

如果想要加總出類型為「正餐」,且花費「大於 100」的總金額,SUMIFS 可以這樣寫:

SUMIFS ( C2:C7 , B2:B7 , “正餐” , C2:C7 , “>100” )

引數名稱解釋範例
sum_range要加總的範圍C2:C7
range1需要檢查條件的範圍1B2:B7
criteria1自訂條件1“正餐”
range2需要檢查條件的範圍2C2:C7
criteria2自訂條件2“>100”

和 SUMIF 一樣的是,自訂條件記得要加上雙引號「”>100″」,只打「>100」 EXCEL 是不會理你的喔!


SUMIFS 自訂條件參照儲存格

如果要用「參照儲存格」的方式,SUMIFS 就需要配合「&」,並且放在雙引號外面

SUMIFS ( C2:C7 , B2:B7 , “正餐” , C2:C7 , “>”&D2 )


SUMIFS 加總符合「日期」多條件的值

如果要計算「日期小於 2024/7/1」,且類型為「維修費用」 的加總,SUMIFS 可以這樣寫:

SUMIFS ( C2:C7 , A2:A7 , “<"&D9 , B2:B7 , “維修費用” )


SUMIF 函數進階用法 – 算出A條件「或」B條件的加總

剛剛提到 SUMIFS 函數可以算出符合 A條件「而且」B條件的加總,但如果我們要算出 A條件「或者」B條件的加總,又需要回到 SUMIF 函數了。

而利用 SUMIF 函數算出符合 A條件「或者」B條件的加總其實很簡單:直接把兩個的總值相加就好!

使用範例:我們想要知道 B 欄中,花費類型是「維修費用」或者「生活開支」的總花費是多少,就可以用 2 個 SUMIF 函數相加:

=SUMIF ( B2:B7 , “維修費用” , C2:C7 ) + SUMIF ( B2:B7 , “生活開支” , C2:C7 )

我們也可以用 SUM 函數合併 2 個 SUMIF:

=SUM (SUMIF ( B2:B7 , “維修費用” , C2:C7 ) ,SUMIF ( B2:B7 , “生活開支” , C2:C7 ) )

但是要特別注意的是,自訂條件時要注意不要設定到「重複條件」,進而加總到重覆的值:

=SUM (SUMIF ( B2:B7 , “維修費用” , C2:C7 ) ,SUMIF ( C2:C7 , “<=3000" , C2:C7 ) )

這個範例中,理想加總應該是 $16,000,但因為 $1000 這筆資料同時符合「維修費用」以及「小於等於 3000」,因此被加總了 2 次。

因此,利用 SUMIF 算 A 條件「或」B 條件的加總時要特別注意「重複加總」的可能性


SUMIF & SUMIFS 常見錯誤

  • #VALUE!:引用的範圍可能有錯誤的數據類型(如#DIV/0!),請確認範圍中的資料是可以加總的值(如數字、文字、日期)。
  • #REF!:引用的範圍可能出現變動(如範圍遭到刪除等等),可以試著再重新拉一次範圍。
  • #NAME:函數可能出現了typo,像是打成「SUMMIF」等等,導致Excel 看不懂函數名稱。

SUMIF & SUMIFS 溫馨提醒

  • SUMIF、SUMIFS 在運算時,一定要加上雙引號「”>100″」
  • 單一條件用 SUMIF
  • 符合 A條件「且」B條件用 SUMIFS
  • 符合 A條件「或」B條件用 SUMIF+SUM

參考資料

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

點擊打開目錄