CODEWITHSUNDEEP
excelexcel-formulacountif
eli-k
eli-k

Reputation: 11360

COUNTIF can't count non-blank formula results?

I am looking for a criterion to use in =countif(range_of_formulas, [criterion]) that will identify non-blank results of formulas. I've found quite a few of those, like "<>" , "<>"&"" , "<>""" , ">""" , "*" and more (e.g. on SO here and here). But each criterion seems to work only in some situations and not others, and they all fail in the following situation:

enter image description here columns A and B have my original data. Column D presents results of the formulas you see in column C. Column F shows results of countif using different criteria (as shown in column E) to count the non-blank results in column D. None of them arrive at the right count - 5.

In my research (and in answers to this question, before I edited to narrow it down) I've seen many workarounds that would get the right count. But what I am asking is if there is a criterion that would work in countif, or if countif cannot reliably count non blank formula results?
(or maybe someone can say under which conditions it can or can't be used).

Upvotes: 9

Views: 15298

Answers (7)

Lovro S
Lovro S

Reputation: 11

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

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

Upvotes: 1

ReturnVoid
ReturnVoid

Reputation: 1126

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...

Upvotes: 4

Sixthsense
Sixthsense

Reputation: 1975

It depends on how you write your formula.

Changing your If() formula like the below and using the Countif as mentioned will result right answer.

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

=COUNTIF(C1:C8,"<>|^|")

Or

If you can't change the If() formula then you have to change the Countif() to Sumproduct() for getting the right results.

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

=SUMPRODUCT(--(TRIM($C$1:$C$8)<>""))

Upvotes: 1

Glitch_Doctor
Glitch_Doctor

Reputation: 3034

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,"?*")

enter image description here

Upvotes: 8

Michael
Michael

Reputation: 4848

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

Upvotes: 4

user4039065
user4039065

Reputation:

COUNTIFS example,

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

Another SUMPRODUCT example,

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

Upvotes: 1

Eleshar
Eleshar

Reputation: 513

This should work:

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

COUNTA - counts all cells with contents

COUNTBLANK - counts all cells evaluating as blank

But beware of cell with no contents, they will increase COUNTBLANK but not COUNTA, derailing the calculation.

Upvotes: 2

Related Questions