SUMIFS(非空白，其他情况) [英] SUMIFS (not blank, and othe conditions)
问题描述
我正在尝试使用 SUMIFS 总结了两个条件.我希望我的总和范围是 A列 ，而我的第一个条件范围是 B列 ，条件是 列B 中包含某些内容，或者本质上不是空白/0.下一个条件是 列C 是否与简单的年份匹配，我可以使之起作用，但是我的问题出在第一次测试的标准上.我已经尝试过:
I am trying to use SUMIFS to sum a couple of conditions. I want my sum range to be column A and my first criteria range is column B, the criteria is that column B has something in it or essentially it isn't blank/0. The next criteria is if column C matches the year which is simple and I can get that to work, but my problem arises from the criteria of the first test. I have tried:
-
SUMIFS(column A, column B, column B > 0, column C, "16") -
SUMIFS(column A, column B, column B <> 0, column C, "16") -
SUM(SUMIFS(column A, column B, "1", column C, "16"), SUMIFS(column A, column B, "2", column C, "16"), SUMIFS(column A, column B, "3", column C, "16")...
SUMIFS(column A, column B, column B > 0, column C, "16")SUMIFS(column A, column B, column B <> 0, column C, "16")SUM(SUMIFS(column A, column B, "1", column C, "16"), SUMIFS(column A, column B, "2", column C, "16"), SUMIFS(column A, column B, "3", column C, "16")...
很显然，我不想使用选项3，但它确实给了我正确的结果.如果我知道B列中的数字始终小于5，那么我可以使用它，但到目前为止，我必须假设B列中的数字可以为0-1000.这里有我想念的东西吗?
如果 B列 不是空白或0，我要做的就是总结 A列 .谢谢.
Obviously, I do not want to use option 3 but it did seem to give me the right result. If I knew that the number in column B would be always under 5 then I may use this but as of now, I have to assume the number in column B can be from 0-1000. Is there something I am missing here?
All I want to do is sum up column A if column B is not blank or 0. Thanks.
推荐答案
您可以通过将标准用引号引起来来进行SUMIFS()比较:
You can do SUMIFS() with comparison as criteria by enclosing your criteria in quotation marks:
=SUMIFS(A:A,B:B,">0")
请注意，>0条件也适用于空白单元格，因为Excel将其评估为零.
Note that >0 criteria also works for blank cells as Excel evaluates them to zero.
但是，如果您采用这种方式:
However, if you do it this way:
=SUMIFS(A:A,B:B,"<>0")
空白单元格将通过条件，只有包含0值的单元格将被跳过.
blank cells will pass the criteria, only cells containing 0 value will be skipped.
这篇关于SUMIFS(非空白，其他情况)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
