Excel: COUNTIF and Nulls/blanks

[Elisha]

How do you use a null in a countif? I supposed I can use Sumprod, "" and an array, but I'm wondering if countif will work? For example, If I have a column of Y or N, I can do =COUNTIF(A1:A100,"Y") or a column of #'s, can do =COUNTIF(A1:A100,">3") to get those great than 3. So how do I do =COUNTIF(A1:A100,cells with text in them)? Thanks.

[thing]

{=count(if(array<>"",array))}, perhaps?

[Elisha]

{=count(if(array<>"",array))}, perhaps?

I did this: {=SUMPRODUCT(--(K3:K16855<>""))} and it worked. I just want to know if it is possilbe with COUNTIF. It actually takes a whole minute or so of thinking for sumprod.

[Walla]

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

or the equivalent

=COUNTIF(A1:A100,"<>")

But, what's wrong with

=COUNTA(A1:A100)

[Elisha]

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

or the equivalent

=COUNTIF(A1:A100,"<>")

But, what's wrong with

=COUNTA(A1:A100)

Nada on all three. You see, I filled the column with =IF(ISERROR(VLOOKUP(four arguments)),"",VLOOKUP(four arguments)). So I have nothing (""), if there is nothing to return/error or it returns what I want it. I want to count filled cells (non "").

[Random Poster]

What about a combination:

=COUNTA(A1:A100)-COUNTIF(A1:A100,"")

It uses more than just COUNTIF but it should be faster than the array formulas.

Another option might be to return something other than "" for the errors. Conditional formatting could be used to hide these errors if needed.


-Hope this helps

[llcooljabe]

Why don't you make it a space, and count the spaces, visually this will give you the same effect.

[Elisha]

What about a combination:

=COUNTA(A1:A100)-COUNTIF(A1:A100,"")

It uses more than just COUNTIF but it should be faster than the array formulas.

Another option might be to return something other than "" for the errors. Conditional formatting could be used to hide these errors if needed.


-Hope this helps

Works! Rather obvious too.:duh: