本教程通過(guò)其語(yǔ)法和用法來(lái)解釋SUMIF和SUMIF函數(shù)之間的差異，并在Excel 365，2021，2019，2019，2016，2013，2013，2010和Lister中提供了許多公式示例，以具有多個(gè)和 /或標(biāo)準(zhǔn)的總和值。

眾所周知，Microsoft Excel提供了一系列功能，可以用數(shù)據(jù)執(zhí)行各種計(jì)算。幾篇文章前，我們探索了Countif和Countifs，這些countif和Countif是根據(jù)單個(gè)條件和幾個(gè)條件進(jìn)行計(jì)數(shù)的。上周，我們介紹了Excel Sumif，以添加符合指定標(biāo)準(zhǔn)的值?，F(xiàn)在是時(shí)候?yàn)g覽sumif的復(fù)數(shù)版本 - excel sumifs，允許通過(guò)多個(gè)標(biāo)準(zhǔn)求和值。

那些熟悉SUMIF功能的人可能會(huì)認(rèn)為將其轉(zhuǎn)換為Sumifs只需要額外的“ S”和一些其他標(biāo)準(zhǔn)。這似乎很合乎邏輯……但是“邏輯”與Microsoft打交道并不總是如此:)

excel sumif函數(shù) - 語(yǔ)法和用法

SUMIF函數(shù)用于基于單個(gè)標(biāo)準(zhǔn)有條件地總和值。我們?cè)谏弦黄恼轮性敿?xì)討論了Sumif語(yǔ)法，這只是一個(gè)快速的復(fù)習(xí)。

• 范圍- 需要您的標(biāo)準(zhǔn)評(píng)估的細(xì)胞范圍。
• 標(biāo)準(zhǔn)- 必須滿足的條件。
• sum_range-如果滿足條件，可選的單元格。

如您所見(jiàn)，Excel Sumif函數(shù)的語(yǔ)法僅允許一個(gè)條件。盡管如此，我們?nèi)匀徽f(shuō)Excel Sumif可用于使用多個(gè)標(biāo)準(zhǔn)的總和值。那怎么可能？通過(guò)添加幾個(gè)SUMIF函數(shù)的結(jié)果以及使用帶有數(shù)組標(biāo)準(zhǔn)的SUMIF公式，如下示例所示。

excel sumifs函數(shù) - 語(yǔ)法和用法

您可以在Excel中使用SUMIF來(lái)根據(jù)多個(gè)標(biāo)準(zhǔn)找到有條件的值。 SUMIFS功能是在Excel 2007中引入的，可在Excel 2010、2013、2016、2019、2021和Excel 365的所有后續(xù)版本中使用。

與sumif相比，sumifs語(yǔ)法更為復(fù)雜：

前3個(gè)論點(diǎn)是強(qiáng)制性的，額外的范圍及其相關(guān)標(biāo)準(zhǔn)是可選的。

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

筆記：

• 為了使SUMIFS公式正確工作，所有criteria_range參數(shù)必須具有與sum_range相同的維度，即相同數(shù)量的行和列。
• SUMIFS函數(shù)可與邏輯一起使用，這意味著總和范圍內(nèi)的單元格僅在符合所有指定標(biāo)準(zhǔn)時(shí)才求和，即所有標(biāo)準(zhǔn)對(duì)于該單元格是正確的。

基本的sumifs公式

現(xiàn)在，讓我們看看具有兩個(gè)條件的Excel Sumifs公式。假設(shè)您有一張列出了來(lái)自不同供應(yīng)商的水果貨物的表。您在A列中有水果名稱，B列中的供應(yīng)商名稱，以及C列中的數(shù)量。您想要的是找出與給定水果和供應(yīng)商有關(guān)的金額，例如Pete提供的所有蘋(píng)果。

當(dāng)您學(xué)習(xí)新事物時(shí)，從簡(jiǎn)單的事情開(kāi)始總是一個(gè)好主意。因此，首先，讓我們?yōu)槲覀兊膕umifs公式定義所有論點(diǎn)：

• sum_range -c2：c9
• criteria_range1 -a2：a9
• 標(biāo)準(zhǔn)1-“蘋(píng)果”
• criteria_range2 -b2：b9
• 標(biāo)準(zhǔn)2-“皮特”

現(xiàn)在組裝上述參數(shù)，您將獲得以下SUMIFS公式：

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

為了進(jìn)一步完善公式，您可以用單元參考替換文本標(biāo)準(zhǔn)“蘋(píng)果”和“ Pete”。在這種情況下，您無(wú)需更改公式即可計(jì)算來(lái)自其他供應(yīng)商的其他水果的數(shù)量：

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

筆記。本質(zhì)上，sumif和sumifs函數(shù)都對(duì)病例不敏感。要讓他們識(shí)別文本案例，請(qǐng)參閱Excel中的對(duì)案例敏感的sumif和sumifs公式。

empel中的sumif vs.

由于本教程的目的是涵蓋通過(guò)幾個(gè)條件匯總值的所有可能方法，因此我們將討論具有兩個(gè)函數(shù)的公式示例 - Excel sumifs和sumifs and sumif具有多個(gè)標(biāo)準(zhǔn)。要正確使用它們，您需要清楚地了解這兩個(gè)功能具有什么共同點(diǎn)以及它們不同的方式。

盡管共同的部分是明確的（相似的目的和參數(shù)），但差異并不是那么明顯，盡管非常重要。

SUMIF和SUMIF之間存在4個(gè)主要差異：

1. 條件數(shù)。 SUMIF一次只能評(píng)估一個(gè)條件，而SUMIF可以檢查多個(gè)標(biāo)準(zhǔn)。
2. 語(yǔ)法。使用sumif， sum_range是最后一個(gè)和可選的參數(shù) - 如果未定義，則將范圍參數(shù)中的值求和。使用sumifs， sum_range是第一個(gè)和必需的參數(shù)。
3. 范圍的大小。在sumif公式中，只要您的左上單元格向右， sum_range不一定必須具有與范圍相同的大小和形狀。在Excel Sumifs中，每個(gè)criteria_range必須包含與sum_range參數(shù)相同數(shù)量的行和列。

   例如，sumif（a2：a9，f1，c2：c18）將返回正確的結(jié)果，因?yàn)?em>sum_range參數(shù)（C2）中最左的單元格是正確的。因此，Excel將自動(dòng)進(jìn)行校正，并在sum_range中包含與范圍一樣多的列和行。

   帶有不等大小范圍的Sumifs公式將返回#Value！錯(cuò)誤。

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

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

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

片刻之前，我們討論了一個(gè)簡(jiǎn)單的Sumifs公式，并具有兩個(gè)文本標(biāo)準(zhǔn)。以相同的方式，您可以使用具有數(shù)字，日期，邏輯表達(dá)式和其他Excel功能的多個(gè)標(biāo)準(zhǔn)的Excel Sumifs。

示例1。與比較運(yùn)算符的Excel Sumifs

在我們的水果供應(yīng)商表中，您想將Mike的所有交付與數(shù)量總結(jié)。 200或更多。為此，您在標(biāo)準(zhǔn)中使用比較操作員“大于或等于”（> =）2，并獲取以下SUMIFS公式：

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

筆記。請(qǐng)注意，在Excel Sumifs公式中，邏輯表達(dá)式和比較操作員應(yīng)始終以雙引號(hào)（“”）封閉。

在討論Excel Sumif函數(shù)時(shí)，我們?cè)敿?xì)介紹了所有可能的比較操作員，可以在SUMIFS標(biāo)準(zhǔn)中使用相同的操作員。例如，以下公式返回細(xì)胞C2：C9中所有值的總和，其大于或等于200且小于或等于300的公式。

=SUMIFS(C2:C9, C2:C9,">=200", C2:C9,"<h3 id="示例-使用帶日期的Excel-Sumifs">示例2。使用帶日期的Excel Sumifs</h3> <p>如果您想根據(jù)當(dāng)前日期使用多個(gè)標(biāo)準(zhǔn)匯總值，請(qǐng)?jiān)谀膕umifs標(biāo)準(zhǔn)中使用tover（）函數(shù)，如下所示。如果C列中的相應(yīng)日期在過(guò)去7天內(nèi)，包括今天：</p> <p> <code>=SUMIFS(D2:D10, C2:C10,">="&TODAY()-7, C2:C10,"<p><img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/174709832098143.png" class="lazy" alt="Excel Sumifs和Sumif具有多個(gè)標(biāo)準(zhǔn) - 公式示例" ></p>

筆記。當(dāng)您將另一個(gè)Excel函數(shù)與標(biāo)準(zhǔn)中的邏輯運(yùn)算符一起使用時(shí)，您必須使用ampersand（＆）來(lái)串聯(lián)一個(gè)字符串，例如“ 以類似的方式，您可以在給定的日期范圍內(nèi)使用Excel Sumif函數(shù)將其用于總和值。例如，如果B列中的日期在2014年1月1日至2014年1月31日至2014年3月31日（包括），則以下SUMIFS公式添加了C2：C9中的值。

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

如本示例所示，可以通過(guò)計(jì)算兩個(gè)SUMIF函數(shù)的差來(lái)實(shí)現(xiàn)相同的結(jié)果 - 如何在給定的日期范圍內(nèi)使用SUMIF來(lái)總和值。但是，Excel Sumifs更容易，更容易理解，不是嗎？

示例3。具有空白和非空白細(xì)胞的Excel Sumifs

在分析報(bào)告和其他數(shù)據(jù)時(shí)，您通常需要將匯總值與空單元相對(duì)應(yīng)。

現(xiàn)在，讓我們看看如何在真實(shí)數(shù)據(jù)上使用具有“空白”和“非空白”標(biāo)準(zhǔn)的SUMIFS公式。

假設(shè)您在B列中有一個(gè)訂單日期，C列和數(shù)量中的交貨日期。在第D欄中。您如何找到尚未交付的產(chǎn)品總計(jì)？也就是說(shuō)，您想知道與B列中非空單元相對(duì)應(yīng)的值的總和。第1列中的空單元。

解決方案是使用帶有2個(gè)標(biāo)準(zhǔn)的SUMIFS公式：

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

使用多個(gè)或標(biāo)準(zhǔn)的Excel Sumif

如本教程開(kāi)頭所述，SUMIFS函數(shù)由邏輯設(shè)計(jì)和邏輯設(shè)計(jì)。但是，如果您需要使用多個(gè)或標(biāo)準(zhǔn)匯總值，即至少滿足其中一個(gè)條件時(shí)，該怎么辦？

示例1。sumif sumif

最簡(jiǎn)單的解決方案是總結(jié)通過(guò)幾個(gè)SUMIF函數(shù)返回的結(jié)果。例如，以下公式演示了如何找到Mike和John交付的產(chǎn)品總數(shù)：

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

如您所見(jiàn)，第一個(gè)SUMIF函數(shù)添加了與“ Mike”相對(duì)應(yīng)的數(shù)量，另一個(gè)SUMIF函數(shù)返回與“ John”相關(guān)的金額，然后添加這2個(gè)數(shù)字。

示例2。和sumif帶有數(shù)組參數(shù)

上述解決方案非常簡(jiǎn)單，只有幾個(gè)標(biāo)準(zhǔn)，可能會(huì)很快完成工作。但是，如果您想使用多個(gè)或條件的總和值，則SUMIF SUMIF公式可能會(huì)大大成長(zhǎng)。在這種情況下，更好的方法是在SUMIF函數(shù)中使用數(shù)組標(biāo)準(zhǔn)參數(shù)。讓我們現(xiàn)在檢查這種方法。

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

在上一個(gè)示例中，如果您想將John，Mike和Pete提供的產(chǎn)品總結(jié)，您的數(shù)組標(biāo)準(zhǔn)看起來(lái)像{“ John”，“ Mike”，“ Pete”}。完整的SUMIF函數(shù)為SUMIF(C2:C9, {"John","Mike","Pete"} ,D2:D9) 。

由3個(gè)值組成的數(shù)組參數(shù)迫使您的Sumif公式返回三個(gè)單獨(dú)的結(jié)果，但是由于我們?cè)趩蝹€(gè)單元格中編寫(xiě)公式，因此僅返回第一個(gè)結(jié)果 - 即John提供的產(chǎn)品總數(shù)。要獲取此數(shù)組標(biāo)準(zhǔn)的工作方法，您必須再使用一個(gè)小技巧 - 將您的sumif公式包含在總和函數(shù)中，例如：

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

如您所見(jiàn)，與Sumif Sumif相比，數(shù)組標(biāo)準(zhǔn)使得公式更加緊湊，并讓您在數(shù)組中添加盡可能多的值。

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

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

與文本值不同，數(shù)字不需要在數(shù)組參數(shù)中包裝在雙引號(hào)中。

示例3。

如果您首選的方法是在某些單元格中列出標(biāo)準(zhǔn)，而不是直接在公式中指定它們，則可以將SUMIF與Sumjunction與給定數(shù)組中組件乘以乘以的Sumpropoduct函數(shù)，并返回這些產(chǎn)品的總和。

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

其中g(shù)2：g4是包含您標(biāo)準(zhǔn)的單元格，在我們的情況下，供應(yīng)商的名稱，如下屏幕截圖所示。

但是，當(dāng)然，如果您想：

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

兩個(gè)公式返回的結(jié)果將與您在屏幕截圖中看到的相同：

具有多個(gè)或標(biāo)準(zhǔn)的Excel Sumifs

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

像往常一樣，一個(gè)例子可能有助于更好地說(shuō)明這一點(diǎn)。在我們的水果供應(yīng)商表中，讓我們添加交貨日期（E列），并在10月找到Mike，John和Pete交付的總數(shù)。

示例1。Sumifssumifs

這種方法產(chǎn)生的公式包括很多重復(fù)，看起來(lái)很麻煩，但它易于理解，最重要的是，它有效:)

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

如您所見(jiàn)，您為每個(gè)供應(yīng)商編寫(xiě)一個(gè)單獨(dú)的SUMIFS函數(shù)，并包括兩個(gè)條件 - 等于或大于Oct -1（“> = 10/1/2014”），小于或小于或等于10月31日（“

示例2。帶有數(shù)組參數(shù)的sum＆sumifs

我試圖在SUMIF示例中解釋這種方法的本質(zhì)，因此現(xiàn)在我們可以簡(jiǎn)單地復(fù)制該公式，更改參數(shù)順序（如您所記得的那樣，SUMIF和SUMIF中的不同）并添加了其他標(biāo)準(zhǔn)。所得公式比sumifs更緊湊：

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

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

示例3。

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

因此，假設(shè)供應(yīng)名稱在單元格H1：H3中，啟動(dòng)日期在單元格H4中，并且在單元格H5中結(jié)束日期，我們的sumproduct公式采用以下形狀：

=SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<p><img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/174709832748614.png" class="lazy" alt="Excel Sumifs和Sumif具有多個(gè)標(biāo)準(zhǔn) - 公式示例" ></p>

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

我希望以上解釋有意義。即使不是這樣，只需記住這個(gè)經(jīng)驗(yàn)法則 - 當(dāng)您使用Sumproduct公式中的比較運(yùn)算符時(shí)，請(qǐng)使用雙Unary Operator（ - ）。

在陣列公式中使用Excel總和

如您所記得的那樣，Microsoft在Excel 2007中實(shí)現(xiàn)了SUMIFS函數(shù)。如果某人仍然使用Excel 2003，2000或更早，則必須使用一個(gè)和陣列公式來(lái)添加具有多個(gè)和標(biāo)準(zhǔn)的值。自然地，這種方法也可以在Excel 2013-2007的現(xiàn)代版本中起作用，并且可以認(rèn)為是Sumifs功能的老式對(duì)應(yīng)物。

在上面討論的SUMIF公式中，您已經(jīng)使用了數(shù)組參數(shù)，但是數(shù)組公式是不同的。

示例1。在Excel 2003和更早的Excel中具有多個(gè)和標(biāo)準(zhǔn)的總和

讓我們回到第一個(gè)示例中，我們發(fā)現(xiàn)與給定的水果和供應(yīng)商有關(guān)的數(shù)量總計(jì)：

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

現(xiàn)在，讓我們看看如何在Excel的“無(wú)Sumifs”版本中完成相同的任務(wù)。首先，您寫(xiě)下應(yīng)以范圍=“條件”形式滿足的所有條件。在此示例中，我們有兩個(gè)范圍/條件對(duì)：

條件1：A2：A9 =“蘋(píng)果”

條件2：B2：B9 =“ Pete”

然后，您編寫(xiě)一個(gè)“乘以”所有范圍/條件對(duì)的總和公式，每個(gè)配對(duì)都包含在括號(hào)中。最后一個(gè)乘數(shù)是總和范圍C2：C9在我們的情況下：

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

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

筆記。輸入任何數(shù)組公式時(shí)，必須按Ctrl Shift Enter 。一旦執(zhí)行此操作，您的公式就會(huì)封閉在{卷曲括號(hào)}中，這是一個(gè)視覺(jué)指示，表明正確輸入了數(shù)組公式。如果您嘗試手動(dòng)鍵入牙套，則您的公式將轉(zhuǎn)換為文本字符串，并且它將無(wú)法使用。

示例2?，F(xiàn)代Excel版本中的總和陣列公式

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

假設(shè)您有兩個(gè)列B和C，并且當(dāng)C列中的值更大或等于10時(shí)，您需要計(jì)算C列大于B列的數(shù)量。立即提出的解決方案是使用“總和陣列公式：”

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

沒(méi)有在上述公式中看到任何實(shí)際應(yīng)用嗎？以另一種方式考慮一下：）

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

條件1：B列（有序項(xiàng)目）中的值大于0

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

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

將三個(gè)范圍/條件對(duì)放在一起，您將獲得以下公式：

=SUM((B2:B10>=0)*(B2:B10>C2:C10)*(D2:D10<g2><p><img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/174709833235426.png" class="lazy" alt="Excel Sumifs和Sumif具有多個(gè)標(biāo)準(zhǔn) - 公式示例" ></p> <p>好吧，本教程中討論的公式示例僅刮擦了excel sumifs和sumif函數(shù)真正可以做到的表面。但是希望他們能幫助您指向正確的方向，現(xiàn)在您可以在Excel工作簿中總結(jié)值，無(wú)論您必須考慮多少?gòu)?fù)雜的條件。</p></g2>

以上是Excel Sumifs和Sumif具有多個(gè)標(biāo)準(zhǔn) - 公式示例的詳細(xì)內(nèi)容。更多信息請(qǐng)關(guān)注PHP中文網(wǎng)其他相關(guān)文章！