使用SUMIFS函数进行多个AND OR条件的筛选

Question

我想创建一个简洁的Excel公式，基于一组AND条件和一组OR条件对一列进行求和。

我的Excel表包含以下数据，并为这些列定义了名称:

• Quote_Value (Worksheet!$A:$A) 保存了一个会计值。
• Days_To_Close (Worksheet!$B:$B) 包含一个结果为数字的公式。
• Salesman (Worksheet!$C:$C) 包含文本且是一个名字。
• Quote_Month (Worksheet!$D:$D) 包含公式(=TEXT(Worksheet!$E:$E,"mmm-yy"))，将另一列的日期/时间数转换为基于文本的月份参考。

如果销售员等于JBloggs，Days_To_Close小于等于90且Quote_Month等于以下一个月份(Oct-13, Nov-13或Dec-13)，我想要SUM Quote_Value。

目前，我已经让此功能工作，但它包含了很多重复，我认为我不需要那么多。

=SUM(SUMIFS(Quote_Value,Salesman,"=JBloggs",Days_To_Close,"<=90",Quote_Month,"=Oct-13")+SUMIFS(Quote_Value,Salesman,"=JBloggs",Days_To_Close,"<=90",Quote_Month,"=Nov-13")+SUMIFS(Quote_Value,Salesman,"=JBloggs",Days_To_Close,"<=90",Quote_Month,"=Dec-13"))

我希望能够做类似以下的事情，但我无法确定正确的语法：

=SUMIFS(Quote_Value,Salesman,"=JBloggs",Days_To_Close,"<=90",Quote_Month,OR(Quote_Month="Oct-13",Quote_Month="Nov-13",Quote_Month="Dec-13"))

这个公式没有出错，只是返回了一个0值。但是如果我手动检查数据，就会发现这不正确。我甚至尝试使用TRIM（Quote_Month）来确保数据中没有空格，但我的扩展SUM公式有效表明数据是正确的，这是一个语法问题。有谁能指导我走向正确的方向吗？

- Dominic

Answer 1

您可以像这样使用 SUMIFS： =SUM(SUMIFS(Quote_Value,Salesman,"JBloggs",Days_To_Close,"<=90",Quote_Month,{"Oct-13","Nov-13","Dec-13"})) SUMIFS 函数将返回一个包含 3 个值的“数组”（每个月份一个总计），因此您需要使用 SUM 来对该数组求和并得出最终结果。

请注意这种语法，您只能在公式中具有最多两个带有“OR”条件的条件...如果有两个条件，则其中一个必须使用逗号分隔条件，另一个必须使用分号分隔条件。

如果您需要更多条件，则可以使用 SUMPRODUCT 和 MATCH，例如在您的情况下： =SUMPRODUCT(Quote_Value,(Salesman="JBloggs")*(Days_To_Close<=90)*ISNUMBER(MATCH(Quote_Month,{"Oct-13","Nov-13","Dec-13"},0))) 在这个版本中，您可以使用 ISNUMBER/MATCH 添加任意数量的“OR”条件。

- barry houdini

---

谢谢，这两种方法对我都有效。然而，是否有一种方式可以使用对存储在其他单元格中的文本的引用来指定数组 {"Oct-13","Nov-13","Dec-13"} 中的月份值，例如 {Worksheet!G2,Worksheet!H2,Worksheet!I2}？ - Dominic

没关系，我已经解决了。我用ISNUMBER(MATCH(Quote_Month,{"Oct-13","Nov-13","Dec-13"}替换成了ISNUMBER(MATCH(Quote_Month,G2:I2，其中G2、H2和I2是包含文本Oct-13、Nov-13、Dec-13的单元格。 - Dominic

没错，你可以在SUMPRODUCT和SUMIFS版本中都使用范围。如果你在SUMIFS版本中这样做，那么公式就需要CTRL+SHIFT+ENTER，除非你用SUMPRODUCT代替SUM。 - barry houdini

刚刚看到这篇文章。惊人的技巧！有没有人知道为什么/如何做到的？就是，我明白MATCH(Quote_Month,{"Oct-13","Nov-13","Dec-13"},0)用于生成向量。但为什么这能行仍然是个谜。当我尝试操作时，最显而易见的第一步是计算命中次数，即对向量求和。但它不起作用。函数向导清楚地显示了向量，我也可以看到其中的命中次数。但SUM不会求和。但是SUMPRODUCT会——为什么？回到SUMIFS，我可以看到{}符号将起作用，但不包括任何范围引用（例如G2:I2）——为什么？Excel很奇怪。 - Ollie2893

我真是该死。你说得很对，巴里。{=SUM(SUMIFS(...))} 也可以。你认为哪个更有效率 - =SUMPRODUCT() 还是 {=SUM()}？ - Ollie2893

Answer 2

你可以使用DSUM函数，它会更加灵活。例如如果你想要更改销售员的名称或报价月份，你无需更改公式，只需更改一些条件单元格即可。详细信息请参见以下链接...甚至条件也可以从其他工作表中复制。

链接: http://office.microsoft.com/en-us/excel-help/dsum-function-HP010342460.aspx?CTT=1

- mooneazy

Answer 3

您可以考虑在“Quote_Month”列中引用实际的日期/时间，然后将您的“OR”转换为一些“AND”，例如（假设日期在我选择称为“Quote_Date”的某个地方）

=SUMIFS(Quote_Value,"<=90",Quote_Date,">="&DATE(2013,11,1),Quote_Date,"<="&DATE(2013,12,31),Salesman,"=JBloggs",Days_To_Close)

（我将有趣的条件移到了前面。）

这种方法在这里有效，因为“OR”条件实际上指定了一个日期范围——在其他情况下可能不起作用。

- Mike Woodhouse

Answer 4

Quote_Month（工作表！$D:$D）包含一个公式（=TEXT(Worksheet!$E:$E,"mmm-yy")），用于将另一列中的日期/时间数字转换为基于文本的月份参考。

您可以通过在 Sumproduct 中添加 + 来使用 OR。请参见此示例：

=SUMPRODUCT((Quote_Value)*(Salesman="JBloggs")*(Days_To_Close<=90)*((Quote_Month="Cond1")+(Quote_Month="Cond2")+(Quote_Month="Cond3")))

屏幕截图

- Siddharth Rout

---

我不明白出了什么问题，但当我输入这个公式时，会出现一个 #VALUE! 错误，表示其中一个公式值的数据类型错误。 - Dominic

除非我看到你的数据，否则我不确定:)。如果你注意到了，它对我有效。 - Siddharth Rout

Answer 5

速度

SUMPRODUCT 比 SUM 数组更快，即在 SUM 函数中使用 {} 数组。 SUMIFS 比 SUMPRODUCT 快30%。

{SUM(SUMIFS({}))} 与 SUMPRODUCT(SUMIFS({})) 都可以正常工作，但是 SUMPRODUCT 更容易编写，不需要使用 CTRL-SHIFT-ENTER 创建 {}。

偏好

我个人更喜欢编写 SUMPRODUCT(--(ISNUMBER(MATCH(...)))) 来处理多个条件。

然而，如果您有一个下拉菜单，想要选择特定的特征或全部，则只能使用 SUMPRODUCT(SUMIFS())。(对于选择“全部”，该值应输入为 "<>" + "任何您想要的单词，只要它不是特定特征的一部分")。

- Walter S

Answer 6

使用以下代码，可以轻松地链接单元格地址...

=SUM(SUMIFS(FAGLL03!$I$4:$I$1048576,FAGLL03!$A$4:$A$1048576,">="&INDIRECT("A"&ROW()),FAGLL03!$A$4:$A$1048576,"<="&INDIRECT("B"&ROW()),FAGLL03!$Q$4:$Q$1048576,E$2))

可以根据需要使用地址/替换/列函数，以完全动态地使用单元格地址。

- Ganesh

Answer 7

为了让公式起作用，请将光标放在公式内，然后按下ctr+shift+enter，这样它就会起作用！

- user3685392