Excel Sumifs和Sumif具有多個標準 - 公式示例

本教程通過其語法和用法來解釋SUMIF和SUMIF函數之間的差異，並在Excel 365，2021，2019，2019，2016，2013，2013，2010和Lister中提供了許多公式示例，以具有多個和 /或標準的總和值。

眾所周知，Microsoft Excel提供了一系列功能，可以用數據執行各種計算。幾篇文章前，我們探索了Countif和Countifs，這些countif和Countif是根據單個條件和幾個條件進行計數的。上週，我們介紹了Excel Sumif，以添加符合指定標準的值。現在是時候瀏覽sumif的複數版本 - excel sumifs，允許通過多個標準求和值。

那些熟悉SUMIF功能的人可能會認為將其轉換為Sumifs只需要額外的“ S”和一些其他標準。這似乎很合乎邏輯……但是“邏輯”與Microsoft打交道並不總是如此:)

• SUMIF函數 - 語法和用法
• sumifs函數 - 語法和用法
• 基本的sumifs公式
• sumif和sumifs之間的差異
• 如何在Excel（和Logic）中使用SUMIF
• 具有多個標準（或邏輯）的Excel Sumif
• 具有多個或條件的Excel Sumifs
• 使用陣列公式中的總和

excel sumif函數 - 語法和用法

SUMIF函數用於基於單個標準有條件地總和值。我們在上一篇文章中詳細討論了Sumif語法，這只是一個快速的複習。

sumif（範圍，標準，[sum_range]）

• 範圍- 需要您的標準評估的細胞範圍。
• 標準- 必須滿足的條件。
• sum_range-如果滿足條件，可選的單元格。

如您所見，Excel Sumif函數的語法僅允許一個條件。儘管如此，我們仍然說Excel Sumif可用於使用多個標準的總和值。那怎麼可能？通過添加幾個SUMIF函數的結果以及使用帶有數組標準的SUMIF公式，如下示例所示。

excel sumifs函數 - 語法和用法

您可以在Excel中使用SUMIF來根據多個標準找到有條件的值。 SUMIFS功能是在Excel 2007中引入的，可在Excel 2010、2013、2016、2019、2021和Excel 365的所有後續版本中使用。

與sumif相比，sumifs語法更為複雜：

sumifs（sum_range，criteria_range1，criteria1，[criteria_range2，criteria2]，…）

前3個論點是強制性的，額外的範圍及其相關標準是可選的。

• sum_range需要一個或多個單元格。這可以是單個單元格，一系列單元格或命名範圍。只有數字的單元格被求和。空白和文本值被忽略。
• criteria_range1需要通過相關標準評估的第一個範圍。
• criteria1 1-必須滿足的第一個條件。您可以以數字，邏輯表達式，單元格參考，文本或其他Excel函數的形式提供標準。例如，您可以使用標準，例如10，“> = 10”，A1，“櫻桃”或今天（）。
• criteria_range2, criteria2, … - 這些是與之相關的其他範圍和標準，可選。您可以在Sumifs公式中使用多達127個範圍/標準對。

筆記：

• 為了使SUMIFS公式正確工作，所有criteria_range參數必須具有與sum_range相同的維度，即相同數量的行和列。
• SUMIFS函數可與邏輯一起使用，這意味著總和範圍內的單元格僅在符合所有指定標準時才求和，即所有標準對於該單元格是正確的。

基本的sumifs公式

現在，讓我們看看具有兩個條件的Excel Sumifs公式。假設您有一張列出了來自不同供應商的水果貨物的表。您在A列中有水果名稱，B列中的供應商名稱，以及C列中的數量。您想要的是找出與給定水果和供應商有關的金額，例如Pete提供的所有蘋果。

當您學習新事物時，從簡單的事情開始總是一個好主意。因此，首先，讓我們為我們的sumifs公式定義所有論點：

• sum_range -c2：c9
• criteria_range1 -a2：a9
• 標準1-“蘋果”
• criteria_range2 -b2：b9
• 標準2-“皮特”

現在組裝上述參數，您將獲得以下SUMIFS公式：

=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")

為了進一步完善公式，您可以用單元參考替換文本標準“蘋果”和“ Pete”。在這種情況下，您無需更改公式即可計算來自其他供應商的其他水果的數量：

=SUMIFS(C2:C9, A2:A9, F1, B2:B9, F2)

筆記。本質上，sumif和sumifs函數都對病例不敏感。要讓他們識別文本案例，請參閱Excel中的對案例敏感的sumif和sumifs公式。

empel中的sumif vs.

由於本教程的目的是涵蓋通過幾個條件匯總值的所有可能方法，因此我們將討論具有兩個函數的公式示例 - Excel sumifs和sumifs and sumif具有多個標準。要正確使用它們，您需要清楚地了解這兩個功能具有什麼共同點以及它們不同的方式。

儘管共同的部分是明確的（相似的目的和參數），但差異並不是那麼明顯，儘管非常重要。

SUMIF和SUMIF之間存在4個主要差異：

1. 條件數。 SUMIF一次只能評估一個條件，而SUMIF可以檢查多個標準。
2. 文法.使用sumif， sum_range是最後一個和可選的參數 - 如果未定義，則將範圍參數中的值求和。使用sumifs， sum_range是第一個和必需的參數。
3. 範圍的大小。在sumif公式中，只要您的左上單元格向右， sum_range不一定必須具有與範圍相同的大小和形狀。在Excel Sumifs中，每個criteria_range必須包含與sum_range參數相同數量的行和列。

   例如，sumif（a2：a9，f1，c2：c18）將返回正確的結果，因為sum_range參數（C2）中最左的單元格是正確的。因此，Excel將自動進行校正，並在sum_range中包含與範圍一樣多的列和行。

   帶有不等大小範圍的Sumifs公式將返回#Value！錯誤。

4. 可用性。 SUMIF在所有Excel版本中都可以使用，從365到2000年。 Sumifs在Excel 2007及更高版本中可用。

好吧，足夠的策略（即理論），讓我們開始遵守策略（即公式示例：）

如何在Excel -Formula示例中使用sumifs

片刻之前，我們討論了一個簡單的Sumifs公式，並具有兩個文本標準。以相同的方式，您可以使用具有數字，日期，邏輯表達式和其他Excel功能的多個標準的Excel Sumifs。

示例1。與比較運算符的Excel Sumifs

在我們的水果供應商表中，您想將Mike的所有交付與數量總結。 200或更多。為此，您在標準中使用比較操作員“大於或等於”（> =）2，並獲取以下SUMIFS公式：

=SUMIFS(C2:C9,B2:B9,"Mike",C2:C9,">=200")

筆記。請注意，在Excel Sumifs公式中，邏輯表達式和比較操作員應始終以雙引號（“”）封閉。

在討論Excel Sumif函數時，我們詳細介紹了所有可能的比較操作員，可以在SUMIFS標準中使用相同的操作員。例如，以下公式返回細胞C2：C9中所有值的總和，其大於或等於200且小於或等於300的公式。

=SUMIFS(C2:C9, C2:C9,">=200", C2:C9,"<h3 id="示例-使用帶日期的Excel-Sumifs">示例2。使用帶日期的Excel Sumifs</h3> <p>如果您想根據當前日期使用多個標準匯總值，請在您的sumifs標準中使用tover（）函數，如下所示。如果C列中的相應日期在過去7天內，包括今天：</p> <p> <code>=SUMIFS(D2:D10, C2:C10,">="&TODAY()-7, C2:C10,"<p><img src="/static/imghwm/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/174709832098143.png?x-oss-process=image/resize,p_40" class="lazy" alt="Excel Sumifs和Sumif具有多個標準 - 公式示例" ></p>

筆記。當您將另一個Excel函數與標準中的邏輯運算符一起使用時，您必須使用ampersand（＆）來串聯一個字符串，例如“ 以類似的方式，您可以在給定的日期範圍內使用Excel Sumif函數將其用於總和值。例如，如果B列中的日期在2014年1月1日至2014年1月31日至2014年3月31日（包括），則以下SUMIFS公式添加了C2：C9中的值。

=SUMIFS(C2:C9, B2:B9, ">=10/1/2014", B2:B9, "

如本示例所示，可以通過計算兩個SUMIF函數的差來實現相同的結果 - 如何在給定的日期範圍內使用SUMIF來總和值。但是，Excel Sumifs更容易，更容易理解，不是嗎？

示例3。具有空白和非空白細胞的Excel Sumifs

在分析報告和其他數據時，您通常需要將匯總值與空單元相對應。

	標準	描述	公式示例
空白細胞	“ =”	與絕對包含的空白單元相對應的總和值 - 沒有公式，沒有零長度字符串。	= sumifs（c2：c10，a2：a10，“ =”，b2：b10，“ =”） 如果A和B列中的相應單元格是絕對空的，則C2：C10中的總和值。
	“”	與“視覺上”空白單元相對應的總和值，其中包括包含其他Excel函數返回的空字符串的單元（例如，具有公式=“”的單元格）。	= sumifs（c2：c10，a2：a10，“”，b2：b10，“”） 單元格中的總和值與上述公式相同，但包括空字符串。
非空白細胞	“ ”	對應於非空單元的總和值，包括零長度字符串。	= sumifs（c2：c10，a2：a10，“ ”，b2：b10，“ ”） 單元格中的總和值C2：如果A和B列中的相應細胞不是空的，包括帶有空字符串的單元。
	sumif或sum / len	與非空單元相對應的總和值，不包括零長度字符串。	= sum（c2：c10） - sumifs（c2：c10，a2：a10，“”，b2：b10，“”） = sum（（c2：c10） *（len（a2：a10）> 0） *（len（b2：b10）> 0）） 單元格中的總和值C2：C10如果列A和B中的相應細胞不是空的，則不包括零長度字符串的單元。

現在，讓我們看看如何在真實數據上使用具有“空白”和“非空白”標準的SUMIFS公式。

假設您在B列中有一個訂單日期，C列和數量中的交貨日期。在第D欄中。您如何找到尚未交付的產品總計？也就是說，您想知道與B列中非空單元相對應的值的總和。第1列中的空單元。

解決方案是使用帶有2個標準的SUMIFS公式：

=SUMIFS(D2:D10, B2:B10,"", C2:C10,"=")

使用多個或標準的Excel Sumif

如本教程開頭所述，SUMIFS函數由邏輯設計和邏輯設計。但是，如果您需要使用多個或標準匯總值，即至少滿足其中一個條件時，該怎麼辦？

示例1。 sumif sumif

最簡單的解決方案是總結通過幾個SUMIF函數返回的結果。例如，以下公式演示瞭如何找到Mike和John交付的產品總數：

=SUMIF(C2:C9,"Mike",D2:D9) SUMIF(C2:C9,"John",D2:D9)

如您所見，第一個SUMIF函數添加了與“ Mike”相對應的數量，另一個SUMIF函數返回與“ John”相關的金額，然後添加這2個數字。

示例2。和sumif帶有數組參數

上述解決方案非常簡單，只有幾個標準，可能會很快完成工作。但是，如果您想使用多個或條件的總和值，則SUMIF SUMIF公式可能會大大成長。在這種情況下，更好的方法是在SUMIF函數中使用數組標準參數。讓我們現在檢查這種方法。

您可以首先列出以逗號分隔的所有條件，然後將所得的逗號分隔列表包裝在{Curly Brackets}中，這在技術上稱為數組。

在上一個示例中，如果您想將John，Mike和Pete提供的產品總結，您的數組標準看起來像{“ John”，“ Mike”，“ Pete”}。完整的SUMIF函數為SUMIF(C2:C9, {"John","Mike","Pete"} ,D2:D9) 。

由3個值組成的數組參數迫使您的Sumif公式返回三個單獨的結果，但是由於我們在單個單元格中編寫公式，因此僅返回第一個結果 - 即John提供的產品總數。要獲取此數組標準的工作方法，您必須再使用一個小技巧 - 將您的sumif公式包含在總和函數中，例如：

=SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9))

如您所見，與Sumif Sumif相比，數組標準使得公式更加緊湊，並讓您在數組中添加盡可能多的值。

這種方法與數字以及文本值一起使用。例如，如果您在C列中而不是供應商的名稱，則具有1、2、3等的供應商ID，那麼您的Sumif公式看起來與此相似：

=SUM(SUMIF(C2:C9, {1,2,3} , D2:D9))

與文本值不同，數字不需要在數組參數中包裝在雙引號中。

示例3。

如果您首選的方法是在某些單元格中列出標準，而不是直接在公式中指定它們，則可以將SUMIF與Sumjunction與給定數組中組件乘以乘以的Sumpropoduct函數，並返回這些產品的總和。

=SUMPRODUCT(SUMIF(C2:C9, G2:G4, D2:D9))

其中g2：g4是包含您標準的單元格，在我們的情況下，供應商的名稱，如下屏幕截圖所示。

但是，當然，如果您想：

=SUMPRODUCT(SUMIF(C2:C9, {"Mike","John","Pete"}, D2:D9))

兩個公式返回的結果將與您在屏幕截圖中看到的相同：

具有多個或標準的Excel Sumifs

如果您想在excel中有條件地匯總值，而不僅僅是多個條件，而是在多個條件下，則必須使用sumifs而不是sumif。公式將與我們剛剛討論的公式非常相似。

像往常一樣，一個例子可能有助於更好地說明這一點。在我們的水果供應商表中，讓我們添加交貨日期（E列），並在10月找到Mike，John和Pete交付的總數。

示例1。 Sumifssumifs

這種方法產生的公式包括很多重複，看起來很麻煩，但它易於理解，最重要的是，它有效:)

=SUMIFS(D2:D9,C2:C9, "Mike", E2:E9,">=10/1/2014", E2:E9, "=10/1/2014", E2:E9, "=10/1/2014" ,E2:E9, "

如您所見，您為每個供應商編寫一個單獨的SUMIFS函數，並包括兩個條件 - 等於或大於Oct -1（“> = 10/1/2014”），小於或小於或等於10月31日（“

示例2。帶有數組參數的sum＆sumifs

我試圖在SUMIF示例中解釋這種方法的本質，因此現在我們可以簡單地複制該公式，更改參數順序（如您所記得的那樣，SUMIF和SUMIF中的不同）並添加了其他標準。所得公式比sumifs更緊湊：

=SUM(SUMIFS(D2:D9,C2:C9, {"Mike", "John", "Pete"}, E2:E9,">=10/1/2014", E2:E9, "

該公式返回的結果與您在上面的屏幕截圖中看到的完全相同。

示例3。

如您所記得的那樣，Sumproduct方法與前兩個不同，您在單獨的單元格中輸入每個條件的方式，而不是直接在公式中指定它們。如果有幾個標準集，則Sumproduct功能不夠，您也必須使用ISNUMBER和匹配。

因此，假設供應名稱在單元格H1：H3中，啟動日期在單元格H4中，並且在單元格H5中結束日期，我們的sumproduct公式採用以下形狀：

=SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<p><img src="/static/imghwm/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/174709832748614.png?x-oss-process=image/resize,p_40" class="lazy" alt="Excel Sumifs和Sumif具有多個標準 - 公式示例" ></p>

許多人想知道為什麼在sumproduct公式中使用雙重破折號（ - ）。關鍵是Excel Sumproduct忽略了除數值以外的所有值，而我們的公式中的比較運算符返回布爾值（true / false），該值是非數字的。為了將這些布爾值轉換為1和0，您使用雙重符號，從技術上講，該標誌稱為雙元運算符。第一個單元脅迫分別對-1/0脅迫。第二個單元否定值，即逆轉符號，將其變成1和0，Sumproduct函數可以理解。

我希望以上解釋有意義。即使不是這樣，只需記住這個經驗法則 - 當您使用Sumproduct公式中的比較運算符時，請使用雙Unary Operator（ - ）。

在陣列公式中使用Excel總和

如您所記得的那樣，Microsoft在Excel 2007中實現了SUMIFS函數。如果某人仍然使用Excel 2003，2000或更早，則必須使用一個和陣列公式來添加具有多個和標準的值。自然地，這種方法也可以在Excel 2013-2007的現代版本中起作用，並且可以認為是Sumifs功能的老式對應物。

在上面討論的SUMIF公式中，您已經使用了數組參數，但是數組公式是不同的。

示例1。在Excel 2003和更早的Excel中具有多個和標準的總和

讓我們回到第一個示例中，我們發現與給定的水果和供應商有關的數量總計：

如您所知，使用普通的sumifs公式可以輕鬆完成此任務： =SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")

現在，讓我們看看如何在Excel的“無Sumifs”版本中完成相同的任務。首先，您寫下應以範圍=“條件”形式滿足的所有條件。在此示例中，我們有兩個範圍/條件對：

條件1：A2：A9 =“蘋果”

條件2：B2：B9 =“ Pete”

然後，您編寫一個“乘以”所有範圍/條件對的總和公式，每個配對都包含在括號中。最後一個乘數是總和範圍C2：C9在我們的情況下：

=SUM((A2:A9="apples") * ( B2:B9="Pete") * ( C2:C9))

如下面的屏幕截圖所示，該公式在最新的Excel 2013版本中完美地工作。

筆記。輸入任何數組公式時，必須按Ctrl Shift Enter 。一旦執行此操作，您的公式就會封閉在{捲曲括號}中，這是一個視覺指示，表明正確輸入了數組公式。如果您嘗試手動鍵入牙套，則您的公式將轉換為文本字符串，並且它將無法使用。

示例2。現代Excel版本中的總和陣列公式

即使在現代版本的Excel中，總和函數的力量也不應被低估。總和陣列公式不僅是思維的體操，而且具有實際價值，如以下示例所示。

假設您有兩個列B和C，並且當C列中的值更大或等於10時，您需要計算C列大於B列的數量。立即提出的解決方案是使用“總和陣列公式：”

=SUM((C1:C10>=10) * (C1:C10>B1:B10))

沒有在上述公式中看到任何實際應用嗎？以另一種方式考慮一下：）

假設您的訂單列表如下屏幕截圖所示，您想知道在給定日期之前還沒有完全交付多少產品。翻譯成Excel的語言，我們有以下條件：

條件1：B列（有序項目）中的值大於0

條件2：C列（交付）中的值少於B列

條件3：D列（到期日）中的日期小於2014年11月1日。

將三個範圍/條件對放在一起，您將獲得以下公式：

=SUM((B2:B10>=0)*(B2:B10>C2:C10)*(D2:D10<g2><p><img src="/static/imghwm/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/174709833235426.png?x-oss-process=image/resize,p_40" class="lazy" alt="Excel Sumifs和Sumif具有多個標準 - 公式示例" ></p> <p>好吧，本教程中討論的公式示例僅刮擦了excel sumifs和sumif函數真正可以做到的表面。但是希望他們能幫助您指向正確的方向，現在您可以在Excel工作簿中總結值，無論您必須考慮多少複雜的條件。</p></g2>

以上是Excel Sumifs和Sumif具有多個標準 - 公式示例的詳細內容。更多資訊請關注PHP中文網其他相關文章！