blocks|key|37420|text|使用对单元格内容的SUMPRODUCT+function的SIGN+function进行计数的LEN+function。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|37421|📷|atomic|37422|​|37423|根据您的示例数据，A1有一个值，A2是由公式返回的零长度字符串，而A3则真正为空。|37424|C2中的公式是，|37425|=SUMPRODUCT(SIGN(LEN(A1:A3)))|style|CODE|37426|entityMap|0|LINK|mutability|MUTABLE|url|https://support.office.com/en-us/article/SUMPRODUCT-function-57a7bfa7-f74d-4ead-8c93-57f759c8f616|1|https://support.office.com/en-us/article/SIGN-function-cfaaf798-9b34-4569-955c-a74a523a7c4a|2|https://support.office.com/en-us/article/len-lenb-functions-6a149d3a-ba2e-4394-ad47-2eb083265a56|3|IMAGE|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/6d1f18f041321c25d4373e84d5351f18.png|imageAlt^0|9|J|0|T|D|1|1B|C|2|0|0|1|3|0|0|0|0|0|T|0^^$0|@$1|2|3|4|5|6|7|18|8|@]|9|@$A|19|B|1A|1|1B]|$A|1C|B|1D|1|1E]|$A|1F|B|1G|1|1H]]|C|$]]|$1|D|3|E|5|F|7|1I|8|@]|9|@$A|1J|B|1K|1|1L]]|C|$]]|$1|G|3|H|5|6|7|1M|8|@]|9|@]|C|$]]|$1|I|3|J|5|6|7|1N|8|@]|9|@]|C|$]]|$1|K|3|L|5|6|7|1O|8|@]|9|@]|C|$]]|$1|M|3|N|5|6|7|1P|8|@$A|1Q|B|1R|O|P]]|9|@]|C|$]]|$1|Q|3|-4|5|6|7|1S|8|@]|9|@]|C|$]]]|R|$S|$5|T|U|V|C|$W|X]]|Y|$5|T|U|V|C|$W|Z]]|10|$5|T|U|V|C|$W|11]]|12|$5|13|U|14|C|$15|16|17|-4]]]]

Use a <a href="https://support.office.com/en-us/article/SUMPRODUCT-function-57a7bfa7-f74d-4ead-8c93-57f759c8f616" rel="nofollow noreferrer">SUMPRODUCT function</a> that counts the <a href="https://support.office.com/en-us/article/SIGN-function-cfaaf798-9b34-4569-955c-a74a523a7c4a" rel="nofollow noreferrer">SIGN function</a> of the <a href="https://support.office.com/en-us/article/len-lenb-functions-6a149d3a-ba2e-4394-ad47-2eb083265a56" rel="nofollow noreferrer">LEN function</a> of the cell contents.

    <img src="https://i.stack.imgur.com/Ph0FY.png" alt="COUNTIF against zero length strings">

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

The formula in C2 is,

<code>=SUMPRODUCT(SIGN(LEN(A1:A3)))
</code>

blocks|key|37472|text|使用SUM而不是COUNTIFS是一种选择吗？如果是这样，我发现它在过滤数据集方面要灵活得多。例如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|37473|=SUM(IF(NOT(ISBLANK(A1:A3))，IF(NOT(ISTEXT(A1:A3))，1，0)，0)|37474|(作为数组公式输入)。IF(NOT(ISBLANK(X)...过滤掉非空格，然后IF(NOT(ISTEXT(x))...过滤掉非文本。任何在过滤器中幸存下来的都会通过加1来计数。你可以根据需要添加任意多的过滤器。如果只想过滤掉空字符串而包括其他文本条目，那么可以使用类似这样的过滤器|37475|IF(ISTEXT(x)，IF(LEN(x)>0,1,0),0)|37476|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

Is using SUM instead of COUNTIFS an option? If so, I've found it to be much more flexible for filtering data sets. For example:

=SUM(IF(NOT(ISBLANK(A1:A3)),IF(NOT(ISTEXT(A1:A3)),1,0),0))

(entered as an array formula). IF(NOT(ISBLANK(x))... filters out non-blanks, then IF(NOT(ISTEXT(x))... filters out non-text. Whatever survives the filters is counted by summing 1. You can add as many filters as necessary. If you wanted to filter out only empty strings but include other text entries you could use a filter like 

IF(ISTEXT(x),IF(LEN(x)>0,1,0),0)

blocks|key|2716445|text|我遇到了这个问题，我刚刚发现了"?*“通配符，它可以搜索任何一个或多个字符，从而避免了空字符串问题--天才！查看Jonathan+Gawrych的答案(紧跟在所选答案之后)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2716446|Excel+Countif+Not+equal+to+string+length+of+zero|offset|length|2716447|不确定这是否适用于OP，因为看起来A1中的值可能需要作为数字而不是字符串来处理，但它可能会对其他来到这里寻找文本解析解决方案的人有所帮助。|2716448|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/23701470/excel-countif-not-equal-to-string-length-of-zero^0|0|0|1C|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@$D|R|E|S|1|T]]|A|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|O]]]]

I was having this exact problem, and I just found out about the "?*" wildcard which searches for any one or more characters, thus avoiding the empty string problem--genius! See Jonathan Gawrych's answer (posted right after the selected answer) here:

<a href="https://stackoverflow.com/questions/23701470/excel-countif-not-equal-to-string-length-of-zero">Excel Countif Not equal to string length of zero</a>

Not sure if this works for the OP, since it looks like the value in A1 could need to be handled as a number not a string, but it might help anyone else who arrived here looking for a text-parsing solution.

I want to get a formula with <code>COUNTIFS</code>, like

<pre><code>=COUNTIF(A1:A3,"&lt;&gt;"&amp;"")
</code></pre>

such that when <code>A1</code> = 2, <code>A2</code> = "", <code>A3</code> = empty, it returns 1.

Notes:

<ol>
<li><code>A2</code> contains an empty string, as the result of a formula. <code>A3</code> is a blank cell, with no formulas in it.</li>
<li>The formula posted returns 2.</li>
<li>I tried using various numbers of double quotes. I always get 2.</li>
<li>I tried using <code>&amp;CHAR(34)&amp;CHAR(34)</code>. I get 2.</li>
<li>The solution posted in <a href="https://stackoverflow.com/questions/772225/how-do-i-get-countifs-to-select-all-non-blank-cells-in-excel">How do I get countifs to select all non-blank cells in Excel?</a> is what I tried, it returns 2 (not useful).</li>
<li>The formula would actually be <code>=COUNTIFS(range1,cond1,range2,cond2)</code>, that is why I cannot use something like 
<code>=ROWS(A1:A3)-COUNTIF(A1:A3,"")</code> or <code>=ROWS(A1:A3)-COUNTBLANK(A1:A3)</code> (see <a href="https://www.ablebits.com/office-addins-blog/2014/07/02/excel-countif-examples/" rel="nofollow noreferrer">this</a>).
<code>range1</code> and <code>range2</code> would come from expressions with <code>INDIRECT</code>, but that is probably not relevant.</li>
<li>I have worked it out with <code>=SUMPRODUCT(--(expression1),--(ISNUMBER(A1:A3)))</code>, but I am specifically asking about the possibility of using <code>COUNTIFS</code>. Discrimination of number vs. text (e.g.) is not relevant at this point.</li>
<li>Blank vs. Empty string is the source of "troubles" (see, e.g., <a href="https://stackoverflow.com/a/27670252/2707864">this</a>).</li>
<li>Excel itself is somewhat ambiguous with respect to the definition of BLANK. In my example, <code>ISBLANK(A2)</code> returns <code>FALSE</code>, but <code>COUNTBLANK(A2)</code> returns <code>1</code>.</li>
<li>I am not interested in a user <code>Function</code>.</li>
</ol>

Have COUNTIFS ignore all blank cells (= empty cells and cells containing "")

我想用COUNTIFS得到一个公式，比如=COUNTIF(A1:A3,"<>"&"")这样当A1 = 2，A2 = ""，A3 = empty时，它返回1。备注：作为公式的结果，A2包含空字符串。A3是一个空白单元格，其中没有公式。发布的公式返回2。我尝试使用不同数量的双引号。我总是得到2.我试着用&CHAR(34)&...

问让COUNTIFS忽略所有空单元格(=空单元格和包含“”的单元格)
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问让COUNTIFS忽略所有空单元格(=空单元格和包含“”的单元格)EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问让COUNTIFS忽略所有空单元格(=空单元格和包含“”的单元格)
EN