SUMIFS(非空白，其他情况)

本文介绍了SUMIFS(非空白，其他情况)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试使用 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.