image

正如我在许多博客中提到的那样,SUMPRODUCT是一种非常通用的功能,可以用于多种用途。在本文中,我们将看到如何使用此函数对具有多个OR条件的值进行计数。

用倍数或条件计算的公式

=SUMPRODUCT(–(((criteria1)(criteria2*)…​)>0)

Criteria1:这是任何返回TRUE和FALSE数组的条件。

Criteria2:这是您要检查的下一个条件。

同样,您可以根据需要设置任意多个条件。

上面的通用公式经常进行修改,以适合使用多个OR标准进行计数的要求。但是基本公式是这样。首先,我们将通过一个示例了解其工作原理,然后,我们将讨论其他需要稍微修改此公式的方案。

示例:如果使用SUMPRODUCT

image

计算经销商代码或年份匹配,则对用户进行计数因此,这里有一个销售人员数据集。数据包含许多列。我们需要做的是计算具有代码“ INKA”或年份为“ 2016”的用户数量。确保如果某人同时拥有(代号为“ inka”和2016年),则应计为1。

因此,这里有两个条件。我们使用上述SUMPRODUCT公式:

=SUMPRODUCT(–(Code=I3)+(Year=K3>0))

在这里,代码和年份被命名为范围。

返回7。在数据中,我们有5条INKA代码记录和4条2016年记录。但是有2个记录分别以“ INKA”和2016作为代码和年份。

这些记录计为1。这就是我们得到7的方式。

如何运作?

image

因此,让我们看一下如何逐步解决该公式,然后我将讨论其工作原理。

第一步,将I3的值(“ INKA”)与代码范围内的每个单元格进行比较。这将返回一个TRUE和FALSE数组。每次比赛都为TRUE。为了节省空间,我没有显示所有的TRUE-FALSE。同样,K3(2016)的值与年份范围内的每个单元格匹配。

在下一步中,我们将这两个数组相加,得到一个新的数值数组。如您所知,在Excel中将TRUE视为1,将FALSE视为0。因此,将TRUE和TRUE相加时,我们得到2,其余的您可以理解。

=SUMPRODUCT*(–(Code=I3)+(Year=K3>0))

1⇒`SUMPRODUCT*`(–\{TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;…​}+\{FALSE;FALSE;FALSE;TRUE;TRUE;…​})>0

2⇒`SUMPRODUCT*`(–\{1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0

3⇒`SUMPRODUCT*`(–(\{TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;…​})

4⇒`SUMPRODUCT*`(\{1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})

5⇒7

在下一步中,我们检查数组中哪个值大于0。

这再次将数组转换为真假数组。对于得到的每个0值,False和rest被转换为true。现在,数组中TRUE值的数目就是我们的答案。但是,我们如何计算它们呢?这是如何做。

链接:/ tips-the-double-negatives-in-excel [双负号(-)号]用于将布尔值转换为1和0。

因此,数组中的每个TRUE值都将转换为1,而FALSE则将转换为0。在最后一步,SUMPRODUCT对这个数组求和,我们得到的答案为7。

使用SUMPRODUCT添加更多或标准进行计数因此,如果您需要添加更多或标准进行计数,则只需在函数中使用+号添加标准即可。例如,如果您想在上述公式中添加另一个条件,以便它增加了销售5种以上产品的员工人数。

SUMPRODUCT公式将如下所示:

简单!是不是

但是,假设您要从Code *范围获得两个条件。假设您要计算“ INKB”。那你怎么做呢?一种方法是使用上述技术,但这将是重复的。假设我要从同一范围再添加10个条件。在这种情况下,使用SUMPRODUCT进行计数时这种技术并不明智。

=SUMPRODUCT(–(Code=I3)(Year=K3)(Sales>5>0))

假设我们有这样排列的数据。

标准代码在I2:J2行中。数据的排列在这里很重要。 3 OR条件计数设置的SUMPRODUCT公式为:

image

_这是当连续写入一个范围中的多个条件时,使用多个条件进行计数的SUMPRODUCT公式。_这将返回正确的答案,即10。

如果您在J3中键入任何年份,该公式也会添加该计数。

=SUMPRODUCT(–(Code=I2:J2)+(Year=I3:J3>0))

当条件在一行中时使用。当条件在同一范围内的一列中时,它将起作用吗?不,不会。

在此示例中,我们有多个要计数的代码,但是这些类型代码写在一栏中。当我们使用上面的SUMPRODUCT公式时,我们收到#N / A错误。我们将不讨论此错误的产生原因,因为这会使本文过长。让我们看看如何使这项工作有效。

image

为了使该公式生效,您需要将代码标准包装在“ link:/ mathematical-functions-excel-transpose-function [TRANSPOSE function]”中。这将使公式起作用。

当一列中列出标准时,这是用于计算多个或相同范围内的条件的公式。

image

是的,队友,我希望我足够清楚,这是有道理的。我希望它能满足您在这里的目的。如果此公式不能解决您的问题,请在下面的评论部分中告诉我您的要求。我将竭诚为您提供任何帮助。您可以提及与Excel / VBA有关的任何疑问。然后,继续学习,保持卓越。

=SUMPRODUCT(–(Code=TRANSPOSE(H3:H4+(Year=TRANSPOSE(I3:I4)))>0))

image

相关文章

链接:/ summing-excel-sumproduct-function [如何在Excel中使用SUMPRODUCT函数]:在excel中将多个数组中的值相乘后返回SUM。此功能可用于执行多项任务。这是最通用的功能之一。

link:/ counting-countifs-dynamic-criteria-range [具有动态条件范围的COUNTIFS]:要使用动态条件范围进行计数,我们只需使用INDIRECT函数。此函数可以`link:// counting-countifs-with-or-for-multi-criteria [COUNTIFS With OR for Multiple Criteria]:对具有多个条件的单元格进行计数,这些单元格使用OR函数匹配。要将OR逻辑放入COUNTIFS函数中,则无需使用OR函数。

在Microsoft Excel中将IF与AND / OR函数一起使用:这些逻辑函数用于进行多个条件计算。对于IF,OR和AND函数用于包含或排除匹配项。

如何在Microsoft Excel中使用OR函数:该函数用于在多个条件中包括所有TRUE值。 link:/ logical-formulas-count-cells-contain-this-or-that [如何计算Excel中Excel中包含此或那个单元格的单元格]’:对于包含此或那个单元格,我们可以使用SUMPRODUCT功能。这是您进行这些计算的方式。

热门文章:

`链接:/ keyboard-formula-shortcuts-50-excel-shortcuts可提高您的生产率[50 Excel快捷方式以提高生产率]更快地完成任务。这50个快捷键将使您在Excel上的工作速度更快。

link:/ vlookup-functions / formulas-and-functions-introduction- [如何使用Excel VLOOKUP函数]|这是excel中最常用和最受欢迎的功能之一,用于从不同范围和工作表中查找值。 link:/ tips-countif-in-microsoft-excel [如何使用]

link:/ vlookup函数的公式和函数介绍[Excel]

link:/ tips-countif-in-microsoft-excel [COUNTIF函数]|使用此惊人的功能对条件进行计数。

您无需过滤数据即可计算特定值。 Countif功能对于准备仪表板至关重要。

如何在Excel中使用SUMIF函数这是仪表板的另一个重要功能。这可以帮助您汇总特定条件下的值。