COUNTIF can't count results without spaces?

Question

I am looking for criteria to use in =countif(range_of_formulas, [criterion]) , which will determine non-empty formula results. I found quite a few such as "<>" , "<>"&"" , "<>""" , ">""" , "*" , etc. (For example, on SO here and here ). But each criterion seems to work only in some situations, and not in others, and they do not work in the following situation:

columns A and B have my original data. Column D presents the results of the formulas that you see in column C. Column F shows the results of countif using different criteria (as shown in column E) to calculate non-empty results in column D. None of them achieve the correct count - 5.

In my research (and the answers to this question, before I edited it to narrow it down), I saw many workarounds that would get the right score. But I ask if there is a criterion that will work in countif , or if countif cannot reliably calculate the results without an empty formula?
(or maybe someone can say under what conditions it can or cannot be used).

Answer 1

This can be achieved with a simple change in the first forum to make the output be a string value (if you need to perform a calculation with numerical results, multiply them by 1 to convert back to a number when they are used)

I simply appended the empty string to the end of the result using the first formula and wrapped it in brackets to keep the happy expression:

=(IF(AND(B1=1,ISBLANK(A1)=FALSE),A1,"")&"")

Now you can use wildcards for any character ( ? ) And any string length ( * ) together as criteria to achieve the desired result in 5:

=COUNTIF($D$1:$D$8,"?*")

Answer 2

No, It is Immpossible. COUNTIF analyzes the condition using some interpretation that is different from comparisons in normal Excel formulas.

In double quotes, the comparison operator at the beginning can be parsed: =,>,> =, <=, <, <>

After that, everything will be either a number or a string. Everything that cannot be parsed as a number will be parsed as a string. A comparison is then performed depending on whether it is a number or a string.

Comparison of numbers ignores strings and comparisons of strings ignore numbers.

"> 3" will count all numbers greater than 3. It ignores all lines.

"> c" will count all lines greater than c (that is, everything starting with c, followed by another character or something, starting with a higher character code). He ignores all numbers.

"> 3 *" will count all lines more than character 3.

When you try to make a ">" ", the character" "is used to denote the character" (because it is enclosed in double quotation marks in the formula), so the comparison you are actually doing here: more than "character. Raise the Unicode diagram and you’ll only see it! less than. " So, if you put, followed by something or "by itself, if your data, you will get fewer points."

Similarly, ">" "" "is simply compared to a string consisting of two double quotes, not an empty string.

Cannot pass empty string to COUNTIF function.

You will need another solution:

• Modify the previous IF statements to return any value other than nullstring that can be verified in the COUNTIF statement. You can even use CHAR(1) to display a non-printable character that appears blank but can still be excluded in COUNTIF: =COUNTIF(D1:D8,"<>"&CHAR(1))

• Use multiple COUNTIFs that count the number of lines and the number of numbers: =COUNTIF(D1:D8,"?*")+COUNTIF(D1:D8,">0") (> 0 is used provided that there are only positive numbers in Otherwise, you will also need to add the number of numbers that are equal to <= 0)

• Use other features suggested by other users.

Answer 3

As already mentioned, not directly without assistance. Thus,

Probably the easiest way to adapt this could be:

 =SUM(COUNTIF($D$1:$D$8,{">0",""})) 

or

 =SUM(COUNTIFS($D$1:$D$8,{">0",""})) 

Which will add accordingly and give you the result. Note the difference between how this COUNTIFS is used compared to yours and placement {}.

Run: https://exceljet.net/formula/countifs-with-multiple-criteria-and-or-logic

To calculate based on several criteria using OR logic, you can use the COUNTIFS function with an array constant. The default function is COUNTIFS AND. When you provide multiple conditions, all conditions must match in order to generate an invoice. One solution is to provide several criteria in an array constant ...

Answer 4

This should work:

 =(COUNTA(range) - COUNTBLANK(range)) 

COUNTA - counts all cells with content

COUNTBLANK - Counts all cells evaluating as empty

But be careful with a cell without content, they will increase COUNTBLANK, but not COUNTA, resetting the calculations.

Answer 5

COUNTIFS example,

 =COUNTIFS(A1:A8, "<>", B1:B8, 1) 

Another example of SUMPRODUCT,

 =sumproduct(sign(len(d1:d10))) 

Answer 6

It depends on how you write the formula.

Changing the If () formula as shown below and using Countif as indicated will result in the correct answer.

 =IF(AND(B1=1,ISBLANK(A1)=FALSE),A1,"|^|") =COUNTIF(C1:C8,"<>|^|") 

or

If you cannot change the If () formula, you need to change Countif () to Sumproduct () to get the correct results.

 =IF(AND(B1=1,ISBLANK(A1)=FALSE),A1,"") =SUMPRODUCT(--(TRIM($C$1:$C$8)<>"")) 

Answer 7

Try this to count non-empty returns if you have formulas that also return empty results. Works with arrays ..

 =SUMPRODUCT(N(LEN(A1:A10)>1))