将 SUMIFS 与多个 AND OR 条件一起使用 [英] Using SUMIFS with multiple AND OR conditions
问题描述
我想创建一个简洁的 Excel 公式，根据一组 AND 条件加上一组 OR 条件对列求和.
I would like to create a succinct Excel formula that SUMS a column based on a set of AND conditions, plus a set of OR conditions.
我的 Excel 表包含以下数据，并且我为列使用了定义的名称.
My Excel table contains the following data and I used defined names for the columns.
- 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"))，用于将日期/时间数字从另一列转换为基于文本的月份参考.
如果 Salesman 等于 JBloggs 且 Days_To_Close 等于或小于 90 且 Quote_Month 等于以下之一(10 月 13 日、11 月 13 日或 12 月 13 日)，我想对 Quote_Value 求和.
I want to SUM Quote_Value if Salesman equals JBloggs and Days_To_Close is equal to or less than 90 and Quote_Month is equal to one of the following (Oct-13, Nov-13, or Dec-13).
目前，我已经完成了这项工作，但其中包含大量重复，我认为我不需要.
At the moment, I've got this to work but it includes a lot of repetition, which I don't think I need.
=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"))
我想做的更像是以下内容，但我无法找出正确的语法:
What I'd like to do is something more like the following but I can't work out the correct syntax:
=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 公式有效这一事实表明数据正常并且这是一个语法问题.任何人都可以引导我朝着正确的方向前进吗?
That formula doesn't error, it just returns a 0 value. Yet if I manually examine the data, that's not correct. I even tried using TRIM(Quote_Month) to make sure that spaces hadn't crept into the data but the fact that my extended SUM formula works indicates that the data is OK and that it's a syntax issue. Can anybody steer me in the right direction?
推荐答案
你可以像这样使用SUMIFS
=SUM(SUMIFS(Quote_Value,Salesman,"JBloggs",Days_To_Close,"<=90",Quote_Month,{"Oct-13","Nov-13","Dec-13"}))
SUMIFS 函数将返回一个包含 3 个值的数组"(Oct-13"、Nov-13"和Dec-13"各一个)，因此您需要 SUM 对该数组求和并给出最终结果.
The SUMIFS function will return an "array" of 3 values (one total each for "Oct-13", "Nov-13" and "Dec-13"), so you need SUM to sum that array and give you the final result.
请注意此语法，公式中最多只能有 两个 条件和OR"条件...如果有两个则合二为一，则必须用 <分隔条件strong>逗号，另一个用分号.
Be careful with this syntax, you can only have at most two criteria within the formula with "OR" conditions...and if there are two then in one you must separate the criteria with commas, in the other with semi-colons.
如果您需要更多，可以将 SUMPRODUCT 与 MATCH 一起使用，例如在你的情况下
If you need more you might use SUMPRODUCT with MATCH, e.g. in your case
=SUMPRODUCT(Quote_Value,(Salesman="JBloggs")*(Days_To_Close<=90)*ISNUMBER(MATCH(Quote_Month,{"Oct-13","Nov-13","Dec-13"},0)))
在该版本中，您可以使用 ISNUMBER/MATCH
In that version you can add any number of "OR" criteria using ISNUMBER/MATCH
这篇关于将 SUMIFS 与多个 AND OR 条件一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
