使用“大于或等于”对文本值进行 SUMIFS

使用“大于或等于”对文本值进行 SUMIFS

使用以下简化的示例,我想计算 Category="Graduation" 和 Date >= 1986-05-01 的总和。

Category    Date      Count
Graduation  1985-06-15  1
Graduation  1985-09-12  2
Graduation  1986-05-21  3
Graduation  1986-06-06  4
Graduation  1986-07-03  7
Transfer    1986-08-14  3
Graduation  1986-08-20  1

日期都是文本,即不是 Excel 日期。日期采用 yyyy-mm-dd 格式,遵循正确的顺序。

以下是我用过的几个公式及其结果,最后引出了我真正的问题:

=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,"='1986-05-21'")     0   Incorrect
=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,"=1986-05-21")       3   Correct
=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,"1986-05-21")        3   Correct

因此,为了表示相等性,您不必在值两边加上引号。这样没问题。

=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,">1986-05-21")       0   Incorrect
=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,">'1986-05-21'")     12  Correct

因此,对于大于,你需要将值括在引号中。

=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,">='1986-05-21'")    12  Incorrect: should be 15

似乎 >= 被当做 > 来处理。

=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,"<'1986-05-21'")     6   Incorrect: should be 3
=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,"<='1986-05-21'")        6   Correct

似乎 < 被视为 <= (即 >= 情况的反面)。

那么,您能帮我调整语法以便 SUMIFS 获取greater thanless than纠正文本值吗?

答案1

COUNTIF(S)/SUMIF(S) 系列函数通常会尽可能尝试将您的数据解释为数字,但这里显然存在一些混淆,因为它似乎不能一致地将您的范围值和标准解释为相同的格式。

在这种情况下:

=SUMIFS(C2:C8,A2:A8,"毕业",B2:B8,">1986-05-21")

尽管格式为文本,Excel 仍会将 B2:B8 中的输入识别为潜在的日期(即数字)值。

但是,与此同时,它(毫无帮助地)将您的标准 - “1986-05-21” - 解释为文本值,因此,由于 Excel 不认为任何数字(这正是 Excel 中的日期 - 21/05/1986 是 31553)“大于”任何文本值(在某个单元格中输入例如 =1000000>“1” - 答案为 FALSE),所以答案为零。

添加撇号时:

=SUMIFS(C2:C8,A2:A8,"毕业",B2:B8,">'1986-05-21'")

Excel 将您的标准和 B2:B8 中的条目都解释为文本,因此直接进行文本到文本的比较也是如此。

您获得 12 而不是 15 的原因是:

=SUMIFS(C2:C8,A2:A8,"毕业",B2:B8,">='1986-05-21'")

是 B4 中的条目是 1986-05-21(没有撇号:公式栏中可能会出现撇号,尽管这在技术上不是字符串的一部分,只是 Excel 表示单元格值是文本的方式),标准是 '1986-05-21'(带有“真正的”撇号)。并且您可以在某个单元格中轻松测试:

="1986-05-21">="'1986-05-21'"

被 Excel 视为 FALSE(我推测 Excel 将后者的前导撇号解释为意味着它比前者“大于” - 我不知道这些解释的来龙去脉:奇怪的是,“=”/a“>”a“返回 FALSE 而“=”'a“>”a“返回 TRUE)。

所有这些也许是改用 SUMPRODUCT 的一个很好的理由,因为它似乎不会出现这些特殊的歧义:

=SUMPRODUCT((A2:A8="毕业")*(B2:B8>="1986-05-21")*C2:C8)

问候

相关内容