Excel总和(如果不是Null字符串) [英] Excel Sum if not Null String

问题描述

我想对B列中与A列中不为空的所有单元格相对应的所有单元格求和.但是，在Excel中，术语不为空"有点模棱两可:如果单元格包含一个公式，但是结果为空字符串，即= IF(1 = 0,1，")，则认为该值不为空，即使结果基本上没有任何结果.

I would like to sum all cells in column B that corresponds to cells in column A which are not empty. However, in Excel, the term "not empty" is a bit ambigious: if a cell contains a formula, but the result is a null string, i.e. =IF(1=0,1,"") , it is considered not empty, even though the result is essentially nothing.

但是，我要排除此类单元格.

However, I want to exclude such cells.

首先要尝试的明显事情是

The obvious thing to try first is

=SUMIF(A:A,"<>",B:B)

但是这不起作用，因为运算符<>仅说一个单元格为空(如果正好是:空).如果其中包含公式，例如= IF(1 = 0,1，")，则将其视为非空"，如上所述.

But this does not work, because the operator <> only says a cell is empty if it is exactly that: empty. If there's a formula in it, such as =IF(1=0,1,""), it is considered "not empty", as explained above.

在我的Google冒险中，我还了解到上面的公式中"="是<>"的反义词，但是据我所知，这对我没有帮助.

In my Google adventures I've also learned that "=" is the converse of "<>" in the formula above, but this does not help me, as far as I can tell.

让我的问题更具挑战性的是，我想在B列中包含与A列中的文本和数字条目相对应的单元格...

What makes my problem even more challengeing is that I want to include cells in column B which correspond to both text and numeric entries in column A...

我可以想到各种变通方法"，但是实际上我在寻找一种解决方案，无需在数据中添加另一列，并且可以将数据保持原样.

I can think of various ways to "work around", but I'm actually after a solution where I don't have to add another column to my data and where I can leave my data as is.

谢谢！

推荐答案

我同意COUNTIF(S)/SUMIF(S)的行为对于空字符串而言令人沮丧.

The behaviour of COUNTIF(S)/SUMIF(S) I agree is a touch frustrating with respect to null strings.

您可以尝试:

=SUM(SUMIF(A:A,{"?*",">=0"},B:B))

如果A列中的数字严格为非负数，或者，如果不是:

if the numbers in column A are strictly non-negative, or, if not:

=SUM(SUMIF(A:A,{"?*",">=0","<0"},B:B))

或者，您可以切换到SUMPRODUCT，它不会受到对空字符串的这种模棱两可的处理:

Alternatively you can switch to SUMPRODUCT, which does not suffer from such ambiguous handling of null strings:

=SUMPRODUCT(0+(A1:A15<>""),B1:B15)

尽管它有一个缺点，与COUNTIF(S)/SUMIF(S)不同，您不能随意地引用任意数量的单元格而不会影响性能(因此，我选择的上一行引用为15):实际上，在SUMPRODUCT中使用整个列引用是一个灾难性的想法.

though which has the drawback that, unlike with COUNTIF(S)/SUMIF(S), you cannot arbitrarily reference as many cells as you wish without detriment to performance (hence my choice of an upper row reference of 15): in fact, the use of entire column references within SUMPRODUCT is a disastrous idea.

致谢

这篇关于Excel总和(如果不是Null字符串)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！