PDA

View Full Version : Excel: COUNTIF and Nulls/blanks

Elisha
08-18-2005, 05:52 PM
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
08-18-2005, 06:20 PM
{=count(if(array<>"",array))}, perhaps?

Elisha
08-18-2005, 06:35 PM
{=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
08-18-2005, 06:37 PM
=COUNTIF(A1:A100,"<>"&"")

or the equivalent

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

But, what's wrong with

=COUNTA(A1:A100)

Elisha
08-18-2005, 07:25 PM
=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
08-18-2005, 10:22 PM

=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
08-18-2005, 10:32 PM
Why don't you make it a space, and count the spaces, visually this will give you the same effect.

Elisha
08-19-2005, 11:53 AM

=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: