SUMIF 與 SUMIFS 是 EXCEL 中30個必學函數之一,用於根據自訂條件對範圍內的數值進行加總,在處理數據時可以節省大量時間。
本文將會用 5 分鐘的時間,教你 SUMIF 與 SUMIFS 函數的用法、彼此的差別、進階用法以及常見錯誤等等。
因此,如果你是第一次接觸 SUMIF 或 SUMIFS 函數,建議可以把用法徹底搞懂,就不用遇到一次就頭痛一次。
那麼我們就開始吧!
SUMIF & SUMIFS 使用時機
兩個函數都可以數出範圍中,符合特定條件的「數值加總」。不過差別是 SUMIF 只能輸入一組條件,SUMIFS 可以輸入很多組條件。
以下是 SUMIF & SUMIFS 的使用時機:
SUMIF 函數用法
SUMIFS 函數用來根據「單個條件」對範圍內的數值進行加總。
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 | 需要檢查條件的範圍1 | B2:B7 |
criteria1 | 自訂條件1 | “正餐” |
range2 | 需要檢查條件的範圍2 | C2: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