我正在尝试使用 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:
很显然,我不想使用选项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(非空白,其他情况)
发布评论