使用以下简化的示例,我想计算 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 than
并less 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)
问候