COUNTIF can't count non-blank formula results?

This can be achieved with a simple change on the first forumla to force the output to be a string value (Should you need to perform a calculation with the numerical results, multiply them by 1 to convert back to number when they are being used)

I have simply joined a blank string to the end of the result on the first formula and wrapped it in brackets to keep excel happy:

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

Now you can use the wildcards for any character (?) and any length of string (*) together as your criteria to achieve your desired total of 5:

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

Nope, it's not possible. COUNTIF parses the condition using some interpretation that is different to comparisons in normal Excel formulas.

Within double quotes, the comparison operator at the start can be parsed: =, >, >=, <=, <, <>

After that, everything is either a number or a string. Anything that can't be parsed as a number will be parsed as a string. The comparison is then performed based on whether it is a number or a string.

Number comparisons ignore strings and string comparisons ignore numbers.

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

">c" will count all strings greater than c (that is, anything starting with c followed by another character or anything starting with a higher character code). It ignores all numbers.

">3*" will count all strings greater than the character 3.

When you try to do ">""", the "" is used to indicate the " character (because it's within double quotes in a formula), so the comparison you're actually doing here is: Everything greater than the " character. Pull up a Unicode chart and you'll see that only ! is less than ". So if you put ! followed by anything or " by itself if your data, you would get one less count.

Similarly, ">""""" just compares to the string consisting of two double quotes rather than a null string.

It's not possible to pass a null string into the COUNTIF function.

You'll need another solution, either:

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

2. Use multiple COUNTIFs that count the number of strings and the number of numbers: =COUNTIF(D1:D8,"?*")+COUNTIF(D1:D8,">0") (>0 is used assuming there are only positive numbers, otherwise you'll need to also add in count of numbers that are <=0)

3. Use other functions as suggested by other users

As already stated, not directly without help. So;

Probably easiest way to adapt this may be:

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

Or

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

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

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

To count based on multiple criteria using OR logic, you can use the COUNTIFS function with an array constant. By default, the COUNTIFS function applies AND logic. When you supply multiple conditions, all conditions must match in order to generate a count. One solution is to supply multiple criteria in an array constant...