让COUNTIFS忽略所有空白单元格(=空单元格和包含“"的单元格) [英] Have COUNTIFS ignore all blank cells (= empty cells and cells containing "")

问题描述

我想使用 COUNTIFS 获得一个公式，例如

I want to get a formula with COUNTIFS, like

=COUNTIF(A1:A3,"<>"&"")

使得当 A1 = 2， A2 ="， A3 =空时，它返回1.

such that when A1 = 2, A2 = "", A3 = empty, it returns 1.

注意:

1. A2 包含一个空字符串，作为公式的结果. A3 是一个空白单元格，其中没有公式.
2. 发布的公式返回2.
3. 我尝试使用各种数量的双引号.我总是得到2.
4. 我尝试使用& CHAR(34)& CHAR(34).我得到2.
5. 发布在如何尝试在Excel中选择所有非空白单元格?是我尝试的方法，它返回2(无用).
6. 公式实际上是 = COUNTIFS(range1，cond1，range2，cond2)，这就是为什么我不能使用类似 = ROWS(A1:A3)-COUNTIF(A1:A3，")或 = ROWS(A1:A3)-COUNTBLANK(A1:A3)(请参见此). range1 和 range2 将来自具有 INDIRECT 的表达式，但这可能不相关.
7. 我已经用 = SUMPRODUCT(-(expression1)，-(ISNUMBER(A1:A3)))进行了计算，但是我特别在问使用的可能性COUNTIFS .此时，区分数字与文字(例如)是无关紧要的.
8. 空白与空字符串是问题"的来源(例如，参见

1. A2 contains an empty string, as the result of a formula. A3 is a blank cell, with no formulas in it.
2. The formula posted returns 2.
3. I tried using various numbers of double quotes. I always get 2.
4. I tried using &CHAR(34)&CHAR(34). I get 2.
5. The solution posted in How do I get countifs to select all non-blank cells in Excel? is what I tried, it returns 2 (not useful).
6. The formula would actually be =COUNTIFS(range1,cond1,range2,cond2), that is why I cannot use something like =ROWS(A1:A3)-COUNTIF(A1:A3,"") or =ROWS(A1:A3)-COUNTBLANK(A1:A3) (see this). range1 and range2 would come from expressions with INDIRECT, but that is probably not relevant.
7. I have worked it out with =SUMPRODUCT(--(expression1),--(ISNUMBER(A1:A3))), but I am specifically asking about the possibility of using COUNTIFS. Discrimination of number vs. text (e.g.) is not relevant at this point.
8. Blank vs. Empty string is the source of "troubles" (see, e.g., this).
9. Excel itself is somewhat ambiguous with respect to the definition of BLANK. In my example, ISBLANK(A2) returns FALSE, but COUNTBLANK(A2) returns 1.
10. I am not interested in a user Function.

推荐答案

使用 SIGN函数-4394-ad47-2eb083265a56"rel =" nofollow noreferrer>单元格内容的LEN函数.

Use a SUMPRODUCT function that counts the SIGN function of the LEN function of the cell contents.

根据您的样本数据，A1有一个值，A2是由公式返回的零长度字符串，而A3实际上是空白.

As per your sample data, A1 has a value, A2 is a zero length string returned by a formula and A3 is truly blank.

C2中的公式是

= SUMPRODUCT(SIGN(LEN(A1:A3)))

这篇关于让COUNTIFS忽略所有空白单元格(=空单元格和包含“"的单元格)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！