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中文网其他相关文章！