SUMIFS函數,如何在Excel中使用該函數對多個條件求和

SUMIFS函數,如何在Excel中使用該函數對多個條件求和

當您必須對許多條件求和時,Excel 中的 Sumifs 函數非常有用。以下詳細介紹如何在 Microsoft Excel 中使用 Sumifs 函數

Excel中的SUMIFS函數是Excel基本函數之一,是Excel中常用的計算函數。在Excel中計算總計時,我們會使用SUM函數,如果我們想要在總計函數中新增某個條件,我們會使用SUMIF函數。當資料表需要多個條件求和的情況下,我們必須使用SUMIFS函數。以下的文章將指導您如何在Excel中使用SUMIFS函數。

文章目錄

Excel 中的 SUMIFS 函數語法

Excel 中的 SUMIFS 函式語法為=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,…)。

在那裡:

  • Sum_range:是​​資料表中需要求和的儲存格,空白值和文字值被忽略,必填參數。
  • Criteria_range1:需要使用準則criteria1進行測試的範圍,為必填值。
  • Criteria1:套用於 criteria_range1 的條件,可以是數字、表達式、儲存格引用,用來決定 criteria_range1 中的哪些儲存格會被求和,也是必要的值。
  • Criteria_range2, criteria2,…:可選的附加範圍和條件,最多 127 對 criteria_range, criteria。

在Excel中使用SUMIFS函數時的注意事項

  • 對於範圍中的每個單元格,當滿足該單元格確定為真的所有相應條件時,將對 sum_range 範圍進行求和。
  • sum_range 中包含 TRUE 的儲存格視為 1,包含 FALSE 的儲存格視為 0。
  • 每個 criteria_range 必須具有與 sum_range 相同的選擇大小,criteria_range 和 sum_range 必須具有相同的行數和相同的列數。
  • 條件可以使用問號 (?) 字符代替單個字符,並使用星號 (*) 代替字串。如果條件是問號或星號,則必須在前面輸入~號,條件值為「.」括起來的文字。
  • 使用 AND 邏輯應用多個條件,即 criteria1 AND criteria2 等。
  • 每個附加範圍必須具有與總範圍相同的行數和列數,但範圍不需要是連續的。如果您提供的範圍不匹配,您將收到 #VALUE 錯誤。
  • 條件中的文字字串必須用引號 ("") 括起來,即“apple”、“">32”、“jap*”
  • 標準中的儲存格引用不能用引號引起來,即「<>
  • SUMIF 和 SUMIFS 可以處理範圍,但不能處理陣列。這意味著您不能在條件範圍上使用其他函數(例如 YEAR),因為結果是一個陣列。如果您需要此功能,請使用 SUMPRODUCT 函數。
  • SUMIFS 和 SUMIF 函數之間的參數順序不同。總和範圍是 SUMIFS 中的第一個參數,但在 SUMIF 中是第三個參數。

Excel 中 SUMIFS 函數的使用說明

使用通配符

通配符,如“*”和“?” 使用 SUMIFS 函數時可以在 criteria 參數中使用。使用這些通配符將幫助您找到相似但不完全匹配的內容。

星號 (*) - 它符合任何字串,可以在之後、之前或與周圍條件一起使用,以允許部分使用搜尋條件。

例如,如果您在 SUMIFS 函數中套用下列條件:

  • N * - 表示以 N 開頭的範圍內的所有儲存格
  • * N - 表示範圍內以 N 結尾的所有儲存格
  • *N* - 含有 N 的細胞

問號 (?) - 符合任何單一字元。假設您應用 N?r 作為標準。“?” 這裡將替換單個字元。N?r 將對應於 North、Nor 等。但是,它不會考慮 Name。

如果給定的資料包含星號或真正的問號怎麼辦?

在這種情況下,您可以使用波形符號 (~)。在這種情況下,您需要在問號前輸入“~”。

將命名範圍與 SUMIFS 函數結合使用

命名範圍是工作表中一組儲存格或儲存格區域的描述性名稱。您可以在使用 SUMIFS 函數時使用命名範圍。

Excel 中 SUMIFS 函數的範例

我們將使用下面的數據表在多種不同的條件下計算總數。

SUMIFS函數,如何在Excel中使用該函數對多個條件求和

範例 1:計算 Hoai 員工銷售的單價低於 400,000 VND 的產品總數。

在結果輸入框中,我們輸入公式 =SUMIFS(D2:D7,C2:C7,"Hoai",E2:E7,"<400000") 然後="" press="">

在那裡:

  • D2:D7是需要計算總項的區域。
  • C2:C7為員工姓名條件取值範圍。
  • 「Hoai」是需要計算區域C2:C7中包含的物品數量的員工姓名的條件。
  • E2:E7 是商品的狀況。
  • 「400,000」是在區域E2:E7中包含物品的條件。

SUMIFS函數,如何在Excel中使用該函數對多個條件求和

結果是項目總數,如下所示。

SUMIFS函數,如何在Excel中使用該函數對多個條件求和

範例2:計算俄羅斯員工銷售的序號為<>的商品總數

在結果輸入框中,我們輸入公式 =SUMIFS(D2:D7,C2:C7,"=Russia",A2:A7,"<5") 然後="" 按="" Enter.="" at =“ 」名稱=“”乘法=“平板電腦=”“nga”=“輸入=”“符號=”或“刪除=””符號=“偶數”>

SUMIFS函數,如何在Excel中使用該函數對多個條件求和

結果,我們得到了俄羅斯員工銷售的序號 < 5="" 的商品總數,即 550 件商品="">

SUMIFS函數,如何在Excel中使用該函數對多個條件求和

例3:計算俄羅斯員工銷售的產品總數,不包括圍巾產品。

在結果輸入框中,我們輸入公式 =SUMIFS(D2:D7,C2:C7,“俄羅斯”,B2:B7,“<>圍巾”)。其中<>符號用於排除資料區中的某個物件作為條件。

SUMIFS函數,如何在Excel中使用該函數對多個條件求和

結果是產品總數,如下所示。

SUMIFS函數,如何在Excel中使用該函數對多個條件求和

範例4:

計算第一代水系神奇寶貝的所有統計數據的總和:

這裡的條件是 Water 和 Generation 為 1。

注意:完整資料集在第 14 行之後繼續,一直到第 759 行。

以下是詳細步驟:

SUMIFS函數,如何在Excel中使用該函數對多個條件求和

  1. 選擇單元格H3
  2. 型別=SUMIFS。
  3. 雙擊SUMIFS 命令。
  4. 選擇總範圍為C2:C759
  5. 類型,
  6. 選擇第一個條件的範圍B2:B759(輸入 1 值)
  7. 選擇條件(儲存格F3,值為Water
  8. 類型 ,
  9. D2:D759從(生成值)中選擇第二個條件的範圍
  10. 類型,
  11. 確定標準(儲存格 G3 的值為 1)。
  12. Enter鍵。

SUMIFS函數,如何在Excel中使用該函數對多個條件求和

注意:您可以在按 Enter 之前重複步驟 9 到 12 來新增更多條件。

該函數現在計算第一代水精靈的總統計量的總和。這個功能可以重複,以便後代可以進行比較。

以上是如何使用 SUMIFS 函數計算具有多個組合條件的總值的一些範例。我們必須合理安排範圍和附帶條件,以便Excel能夠識別函數公式進行計算。

SUMIFS 函數的限制

SUMIFS 函數有一些您應該注意的限制:

  • SUMIFS 中的條件使用 AND 邏輯進行組合。換句話說,所有條件都必須為 TRUE 才能將儲存格包含在總計中。若要使用 OR 邏輯對儲存格求和,您可以在簡單的情況下使用替代解決方案。
  • SUMIFS 函數需要所有範圍參數的實際範圍;你不能使用數組。這意味著您無法執行諸如從 SUMIFS 函數內的日期中提取年份之類的操作。要在應用條件之前更改範圍參數中出現的值,SUMPRODUCT 函數是一個靈活的解決方案。
  • SUMIFS 不區分大小寫。若要根據區分大小寫的條件對值求和,可以使用基於 SUMPRODUCT 函數和 EXACT 函數的公式。

解決上述限制最常見的方法是使用 SUMPRODUCT 函數。在目前版本的 Excel 中,另一種選擇是使用較新的 BYROW 和 BYCOL 函數。

如何修復 Excel SUMIFS 函數中的 #VALUE 錯誤

問題:公式引用了封閉工作簿中的儲存格

引用已關閉工作簿中的儲存格或儲存格區域的 SUMIF 函數將導致 #VALUE! 錯誤。

注意:這是一個常見錯誤。在使用其他 Excel 函數(例如 COUNTIF、COUNTIFS、COUNTBLANK...)時,您也經常遇到此訊息。

解決方案:

開啟此公式中包含的工作簿,按 F9 刷新公式。您也可以透過在陣列公式中使用 SUM 和 IF 函數來解決該問題。

問題:條件字串長度超過 255 個字符

當您嘗試組合長度超過 255 個字元的字串時,SUMIFS 函數會傳回錯誤結果。

解決方案:

如果可能的話,縮短字串。如果不可能,請使用 CONCATENATE 函數或 & 運算子將值拆分為多個字串。例如:

=SUMIF(B2:B12,"long string"&"another long string")

問題:在 SUMIFS 中,criteria_range 參數與 sum_range 參數不一致。

SUMIFS 中的範圍參數必須永遠相同。這表示 criteria_range 和 sum_range 參數應該引用相同數量的行和列。

在以下範例中,公式傳回貝爾維尤的每日蘋果銷售總額。但是,sum_range (C2:C10) 參數與 criteria_range (A2:A12 和 B2:B12) 中的行數和列數不對應。使用公式=SUMIFS(C2:C10,A2:A12,A14,B2:B12,B14)會產生#VALUE 錯誤。

SUMIFS函數,如何在Excel中使用該函數對多個條件求和

解決方案:

更改sum_range為 C2:C12 並再次嘗試該公式。

祝您成功!


Excel中的IFERROR函數、公式及用法

Excel中的IFERROR函數、公式及用法

學習如何在 Excel 中使用非常常用的 IFERROR 函數,提升您的數據處理效率。

如何在 Excel 中使用 VSTACK 和 HSTACK 函數

如何在 Excel 中使用 VSTACK 和 HSTACK 函數

想知道如何在 Excel 中堆疊和附加資料嗎?讓我們一起學習如何使用 Microsoft Excel 中的 VSTACK 和 HSTACK 函數!

SUBTOTAL 函數:公式以及如何在 Excel 中使用 SUBTOTAL 函數

SUBTOTAL 函數:公式以及如何在 Excel 中使用 SUBTOTAL 函數

了解 Excel 中的 SUBTOTAL 函數及其用途,讓計算更簡單有效!

Excel中的除法函數:MOD函數(求餘數)與QUOTIENT函數(求整數部分)

Excel中的除法函數:MOD函數(求餘數)與QUOTIENT函數(求整數部分)

MOD函數是Excel中除法求餘的函數。學習如何輕鬆使用MOD函數和QUOTIENT函數來計算餘數和整數部分,提升您的Excel技能。

在 Excel 上玩“拼圖”

在 Excel 上玩“拼圖”

Reddit 用戶 MehmetSalihKoten 在 Microsoft Excel 中創建了俄羅斯方塊的全功能版本。

如何使用Excel中的轉換功能

如何使用Excel中的轉換功能

要轉換 Excel 中的測量單位,我們將使用 Convert 函數。

Excel中HLOOKUP函數的使用方法

Excel中HLOOKUP函數的使用方法

HLOOKUP 函數是什麼?如何在Excel中使用HLOOKUP函數?讓我們來和 LuckyTemplates 一起找出答案吧!

如何在 Excel 中使用 VALUE 函數

如何在 Excel 中使用 VALUE 函數

Excel中的VALUE函數是什麼?Excel中的數值公式是什麼?讓我們透過 LuckyTemplates.com 來了解一下吧!

Excel EOMONTH函數,EOMONTH函數如何使用

Excel EOMONTH函數,EOMONTH函數如何使用

Excel中的EOMONTH函數用於顯示給定月份的最後一天,實作非常簡單。然後,您將根據我們輸入的數據獲得特定月份的最後一天。

如何在 Excel VBA 中使用 do-while 循環

如何在 Excel VBA 中使用 do-while 循環

想要在 Excel VBA 中自動執行重複任務嗎?因此,讓我們學習如何使用 Do-While 迴圈來實作一系列重複操作,直到滿足條件。