☰

在 SUMIFS 中包含空值和非空值的标准

tag-icon • tag-icon microsoft-excel worksheet-function
在 SUMIFS 中包含空值和非空值的标准

总结：

我想改变配方

=SUMIFS(sumRange, criteriaRange1, criterium1, criteriaRange2, criterium2),

这样，如果满足某个条件，它就会忽略标准 2。假设有一个单元格（“控制单元格”），当必须忽略标准 2 时，该单元格的值为 FALSE，而当必须应用标准 2 时，该单元格的值为 TRUE。

我希望将公式改为如下形式：

=SUMIFS(sumRange, criteriaRange1, criterium1, criteriaRange2, IF(controlCell = FALSE, X, criterium2))
=SUMIFS(sumRange, criteriaRange1, criterium1, IF(controlCell = FALSE, Y, criteriaRange2), criterium2)
=SUMIFS(sumRange, criteriaRange1, criterium1, IF(controlCell = FALSE, Y, criteriaRange2), IF(controlCell = FALSE, X, criterium2))

并且想知道要使用什么值或字符串文字X，或者要使用什么文字Y，以实现这一点。

我目前最喜欢的解决方案是：（1）在表中添加一列，用 填充=TRUE，然后（2）引用此列Y，并使用TRUE。X这可行，但缺点是我需要添加一列（我不想弄乱输入数据）

长读：

我有一张大表，我想计算某一列的总和。我只想包含符合特定条件的行 - 这是典型的情况SUMIFS。

假设这是一张表，名为inv：

在此处输入图片描述

如果我想知道我有多少颗红葡萄，无论大小，我都可以这样做

=SUMIFS(inv[Qty];inv[prd];"Grapes";inv[col];"red")

并得到正确答案10。

到目前为止，一切都很好。

现在，我想知道我有多少个苹果，无论大小和颜色。

通常情况下，这很容易，

=SUMIFS(inv[Qty];inv[prd];"Apple")

给出正确答案28。

但由于我的限制，我必须col在公式中包含 -column。如果您想知道原因，我会在下面添加它，但对于我的问题的解决方案，它是无关紧要的。我需要知道的是，我可以在此列上放置什么条件，以便它接受此列中具有任何（或没有）值的行。（请注意，并非所有苹果行都为颜色设置了值）。

我已尝试过：

=SUMIFS(inv[Qty];inv[prd];"Apple";inv[col];"")
=SUMIFS(inv[Qty];inv[prd];"Apple";inv[col];"*")
=SUMIFS(inv[Qty];inv[prd];"Apple";inv[col];"<>")

第一个使用""作为标准，结果给出了错误的答案12，因为它只选取了没有颜色的行。而其他使用"*"和"<>"作为标准，结果给出了错误的答案16，因为它们只选取了有一种颜色。

有人知道我可以使用什么作为标准，以便它同时包含空行和非空行吗？

有兴趣者请注意：

好的，为什么我需要以SUMIFS这种方式使用公式，即以inv[col]列作为条件范围？

  • 首先，实际上我的表格比这个要长得多，而且我需要做很多事情SUMIFS。因此，还有一个额外的“摘要”表格，这里的例子是

在此处输入图片描述

最后一行颜色列中的空值意味着颜色应被忽略作为标准。如您所见，使用我当前使用的公式（下面会提到），我在该行中得到了一个错误的值，因为我想要得到28。

  • 因为汇总表也很长，所以我在最后一列输入的公式需要统一，即对于这个汇总表的每一行都相同。

  • 在这个小例子中，看起来合乎逻辑的是

=IF([@color]="";SUMIFS( inv[Qty];inv[prd];[@product]);SUMIFS(inv[Qty];inv[prd];[@product];inv[col];[@color]))。

但是，这在我的实际案例中也不是一种选择，因为库存表也相当宽。我有很多列与本例中的颜色列处于相同的情况。（并且每个额外的列都会使公式需要自定义公式的情况数量增加一倍SUMIFS。）

  • 只是为了向你展示我目前拥有的东西：

=SUMIFS(inv[Qty];inv[prd];[@product];inv[col];IF([@color]="";"*";[@color]))

该方法不起作用，因为它只接受非空行，如上所示。我想要此公式的变体，为此，我只需要知道要替换什么"*"才能使所有行都符合条件。

答案1

这是另一种解决方案SUMPRODUCT，它仍然比更复杂SUMIFS，但我觉得这是一个进步：

=SUMPRODUCT(inv[qty]*(inv[prd]=F2)*IF(ISERROR(SEARCH(G2,"#"&inv[col]&"#")),0,1))

这是一个数组公式，因此输入后需要按 CTRL + SHIFT + ENTER。

在此处输入图片描述

笔记：

  • 默认情况下，搜索颜色会查找所有包含条件的条目（例如“黄色”条件也会与“黄红色”匹配）
  • 可以通过使用#术语来查找确切的术语（例如#yellow#）
  • 空条件将匹配所有内容，用于##仅搜索空值

当然，配方可以进行微调。

答案2

解决此问题的一种方法可能是SUMPRODUCT：

=SUMPRODUCT(Table1[Qty],--(Table1[prd]="Apple"),((--(Table1[col]=""))+(--(Table1[col]<>""))))

[col]我们将所有等于""和不等于的实例相加，这样就计算出了所有的苹果，无论颜色如何

答案3

下面显示的方法将获得表中所示颜色的水果和无色的水果的总和。

在此处输入图片描述

怎么运行的：

  • 表范围是A1:C12。
  • 摘要数据范围是A15:B20。

  • 在单元格中输入此公式C15并向下填充以获得和， 使用TABLE 的参数。

    =IF(B15="", SUMIFS(FruitTAB[Qty],FruitTAB[Fruit Name],A15,FruitTAB[Color],""),SUMIFS(FruitTAB[Qty],FruitTAB[Fruit Name],A15,FruitTAB[Color],B15))

如果您的数据是：您可以使用此公式进入SUM单元格D15并填写：not a TABLE

=IF(B15="", SUMIFS($C$2:$C$12,$A$2:$A$12,A15,$B$2:$B$12,""),SUMIFS($C$2:$C$12,$A$2:$A$12,A15,$B$2:$B$12,B15))

注意：

  • 水果标签，是表名。

  • 为了社区的利益，我建议在非表格范围中使用公式 D15。，您和读者可以跳过它。

  • 根据需要调整公式中的单元格引用。

相关内容