2026年11月19日 星期四

Excel SUMIF 與 COUNTIF 教學:依條件加總、計數、平均,一次學會

有一份銷售紀錄,想知道王小明總共賣了多少、平板賣了幾筆、金額超過三萬的有幾筆,你會怎麼做?用篩選之後再手動加總,資料一改就得重做。Excel 的 SUMIF、COUNTIF 這一組「條件統計函數」,可以依照條件自動加總、計數、算平均,資料更新時結果也會跟著更新。

本文以「銷售紀錄」為例,用 5 個步驟帶你學會。使用 Excel 2016 以上的繁體中文版,SUMIFS、COUNTIFS 等多條件函數在 Excel 2007 以後的版本都有。文中的圖片為示意圖,不是實際畫面。

步驟一:認識 SUMIF 的三個參數

SUMIF 的意思是「符合條件才加總」,寫法是:

=SUMIF(條件範圍, 條件, 加總範圍)

  • 條件範圍:要拿來檢查的範圍,例如業務員那一欄。
  • 條件:要符合的內容,例如「王小明」。
  • 加總範圍:符合條件的列,要加總哪一欄的數字,例如金額。

它的兩個親戚是:

  • COUNTIF:符合條件的有幾筆,只需要條件範圍和條件。
  • AVERAGEIF:符合條件的平均,參數和 SUMIF 相同。
=SUMIF(A2:A9,"王小明",C2:C9)① 條件範圍要檢查的範圍例:A2:A9(業務員)② 條件要符合的內容例:「王小明」③ 加總範圍要加總的數字例:C2:C9(金額)COUNTIF 只有前兩個參數(條件範圍、條件),因為只算幾筆,不需要加總範圍。
圖 1 SUMIF 的三個參數(示意圖)

步驟二:寫出第一個 SUMIF

範例資料放在 A2:C9,A 欄是業務員、B 欄是產品、C 欄是金額。要算王小明的總金額:

  1. 點選要放結果的儲存格。
  2. 輸入 =SUMIF(A2:A9,"王小明",C2:C9),按 Enter。

結果是 102,000,也就是王小明三筆的金額 60,000、12,000、30,000 的總和。文字條件要放在雙引號裡,而且要使用半形的符號。

要一次算出每個人,可以把「王小明」改成存放姓名的儲存格 E2,並用 F4 把範圍加上 $ 固定,再向下複製:

=SUMIF($A$2:$A$9,E2,$C$2:$C$9)

ABC1業務員產品金額2王小明筆電60,0003李美玲平板36,0004王小明平板12,0005陳大華筆電30,0006李美玲筆電60,0007陳大華平板24,0008王小明筆電30,0009李美玲平板12,000EFGH1業務員加總筆數平均2王小明102,000334,0003李美玲108,000336,0004陳大華54,000227,000第 2 列的公式:F2=SUMIF($A$2:$A$9,E2,$C$2:$C$9)G2=COUNTIF($A$2:$A$9,E2)H2=AVERAGEIF($A$2:$A$9,E2,$C$2:$C$9)把條件改成 E2,往下複製,就能算出每個人。(黃底是王小明的 3 筆)
圖 2 依業務員統計加總、筆數與平均(示意圖)

步驟三:加上 COUNTIF 與 AVERAGEIF

同樣的概念,在旁邊的欄位寫上:

  • 筆數:=COUNTIF($A$2:$A$9,E2),王小明是 3 筆。
  • 平均:=AVERAGEIF($A$2:$A$9,E2,$C$2:$C$9),王小明平均每筆 34,000。

三個公式都向下複製,一張依業務員統計的小報表就完成了,之後原始資料有增減,這張報表的數字也會跟著改變。

步驟四:條件的各種寫法

條件不是只能寫文字,還有幾種常用的寫法:

  • 直接寫文字:"王小明"。
  • 引用儲存格:直接寫儲存格位址,例如 E2,不需要加引號。
  • 比較條件:數字比較要和符號一起放進雙引號,例如 ">=30000"。如果門檻放在儲存格 F1,則寫成 ">="&F1,用 & 把符號和儲存格連起來。
  • 萬用字元:* 代表任意多個字,? 代表任意一個字,例如 "王*" 會統計所有姓王的。
  • 不等於:"<>筆電",統計不是筆電的資料。
條件類型公式結果文字相同=COUNTIF(A2:A9,"王小明")3 筆引用儲存格=COUNTIF(A2:A9,E2)E2 是李美玲:3 筆比較(大於等於)=COUNTIF(C2:C9,">=30000")5 筆包含(萬用字元)=COUNTIF(A2:A9,"王*")3 筆不等於=COUNTIF(B2:B9,"<>筆電")4 筆(平板)比較符號與數字要一起放進雙引號裡:「>=30000」。
圖 3 COUNTIF 條件的各種寫法與結果(示意圖)

步驟五:多個條件用 SUMIFS 與 COUNTIFS

如果要同時符合兩個以上的條件,例如「王小明賣出的筆電」,就用 SUMIFS:

=SUMIFS(C2:C9,A2:A9,"王小明",B2:B9,"筆電")

結果是 90,000。它的規則和 SUMIF 有兩個差異,要特別留意:

  • 加總範圍放在最前面,和 SUMIF 的順序相反。
  • 後面的「條件範圍、條件」成對排列,可以一組接一組往後加。

COUNTIFS 與 AVERAGEIFS 用法類似,COUNTIFS 沒有加總範圍,直接寫成對的條件,例如 =COUNTIFS(A2:A9,"李美玲",B2:B9,"平板"),結果是 2 筆。

=SUMIFS(C2:C9,A2:A9,"王小明",B2:B9,"筆電")① 加總範圍要加總的數字(放最前面)例:C2:C9(金額)② 第一組條件範圍+條件,成對出現A2:A9 =「王小明」③ 第二組條件可以再加更多組B2:B9 =「筆電」結果:90,000(王小明賣出的筆電:60,000 + 30,000)SUMIFS 的加總範圍放在最前面,和 SUMIF 的順序相反,要特別小心。
圖 4 SUMIFS 的公式結構(示意圖)

常見問題

Q1:結果是 0,但明明有符合的資料?
常見原因有:條件文字前後多了空格、範圍沒有對齊,或是金額欄的數字被存成文字。先檢查資料,再確認公式裡的範圍是否正確。

Q2:SUMIFS 出現 #VALUE!?
所有的範圍大小必須一致,例如都是 2 到 9 列。如果加總範圍是 C2:C9,條件範圍卻是 A2:A10,就會出錯。

Q3:寫了 >=30000,結果卻不對?
比較符號和數字要一起放在雙引號裡,寫成 ">=30000",而且符號要用半形。

Q4:想要「或」的條件,例如王小明或陳大華?
SUMIF 一次只能一個條件。可以把兩個 SUMIF 相加:=SUMIF(A2:A9,"王小明",C2:C9)+SUMIF(A2:A9,"陳大華",C2:C9)。

小結

條件統計的重點是:先分清楚「條件範圍」和「加總範圍」,一個條件用 SUMIF/COUNTIF/AVERAGEIF,多個條件用 SUMIFS/COUNTIFS。如果需要一次看到多個維度的整張統計表,樞紐分析表會更方便;但要在固定的報表中放進特定數字,這一組函數最靈活。

沒有留言:

張貼留言