如何使用 sumif / sumifs 与 or 逻辑?
有时我们需要对公式进行一些小的修改才能使其有效。
就像这样,SUMIF OR。它是一种高级公式,可帮助您提高 SUMIF 的灵活性。是的,你也可以做 SUMIFS。但在使用它之前,让我告诉您有关 SUMIF 的一件事。
在 SUMIF 中,您只能使用一个条件,而在 SUMIFS 中,您可以使用多个条件来求和。
就这样一件事。假设在 SUMIFS 中,如果您指定两个不同的条件,则只有满足这两个条件的单元格才会被添加到一起。
由于它使用 AND 逻辑,因此必须满足要包含的单元格的所有条件。当您将 OR 逻辑与 SUMIF/SUMIFS 结合起来时,您可以同时使用两个不同的条件添加值。
话不多说,让我们来学习这个神奇的公式。
我们真的需要“或”逻辑吗?一个例子?
让我向您展示一个需要在 SUMIFS 或 SUMIF 中添加 OR 逻辑的示例。
查看下面的数据表,其中列出了产品及其数量和当前发货状态。
在此数据中,您有两种类型的产品(有缺陷的和损坏的),您必须将其退回给我们的供应商。
但在此之前,您需要计算两种类型产品的总数量。如果您选择正常方法,则必须应用 SUMIF 两次才能获得此总数。
但如果您在 SUMIF 中使用 OR 条件,则可以通过一个公式获得两个乘积的总和。
好的,让我们将 OR 与 SUMIFS 一起应用
在开始之前,请从此处下载此示例文件 待续。
在电子表格中要计算总计的任何其他单元格中,插入下面的公式并按 Enter。
=SUM(SUMIFS(C2:C21,B2:B21,{"Damage","Faulty"}))
在上面的公式中,您使用了 SUMIFS,但如果您想使用 SUMIF,则可以在单元格中插入以下公式。
=SUM(SUMIF(B2:B21,{"Damage","Faulty"},C2:C21))
使用以上两个公式,您将得到 540 的结果。要仔细检查,只需手动检查总数即可。
这个公式是如何运作的?
正如我所说,SUMIFS 函数使用 AND 逻辑来添加值。但是,您上面使用的公式包含 OR 逻辑。
要理解这个公式,您需要将其分为三个不同的部分。
- 首先要了解您在使用数组概念的公式中使用了两个不同的标准。 从此处了解有关该板的更多信息。
- 第二件事是,当您使用数组指定两个不同的值时,SUMIFS 必须分别搜索这两个值。
- 第三件事是,即使使用数组公式后,SUMIFS 也无法返回单个单元格中两个值的总和。这就是为什么您需要将其加入 SUM 函数。
利用上述概念,您可以在一个单元格中获得两种产品类型的总计。
这与 SUMIF 和 SUMIFS 的工作方式相同。
而且,您还可以通过在 SUMIFS 中的第二个条件中指定产品名称来扩展公式,以获得单个产品的总计。
获取 Excel 文件
下载使用动态标准范围
在评论部分,有人要求我使用单元格引用来添加多个条件,而不是将它们直接插入到公式中。
嗯,这是一个非常有效的问题,您可以使用动态命名范围而不是硬核值来完成它。
为此,您只需对公式进行两个小更改。
- 首先,您应该使用值的命名范围(最好的方法是使用数组),而不是使用大括号。
- 之后,您需要使用 Ctrl+Shift+Enter 输入此公式作为正确的数组公式。
所以现在你的公式将是:
不同列的多个条件
让我带您进入这个公式的下一个层次。看看下面的数据表就知道了。
在此表中,有两个不同的列,具有两种不同的状态类型。现在,您需要从此数据表中添加产品损坏的数量,退货状态为“否”。
这仅意味着尚未退回的损坏和有缺陷的产品。并且,其公式为。
=SUM(SUMIFS(D2:D21,B2:B21,{"Damage","Faulty"},C2:C21,"No"))
获取 Excel 文件
下载结论
使用 OR 技术的最佳部分是您可以向其中添加如此多的条件。如果只想使用 OR 逻辑,可以使用 SUMIF。如果您想在单个公式中创建 OR 条件和 AND 条件,则可以使用 SUMIFS。
您知道,这是我最喜欢的配方技巧之一,希望您发现它有帮助。
现在告诉我一件事。您以前使用过这种公式吗?在评论部分与我分享您的想法,我很乐意听取您的意见,并且不要忘记与您的朋友分享此技巧。