Correction

8-6=2  2 is made up by a blank and a null string.  As discussed previously, there doesn't seem to be a way to differentiate between blank and null when COUNTIF is used.

Roger, sometimes blank is treated as zero and sometimes it is not even for the same function COUNTIF.  In other words, I have to do trial an error and very detailed testing for each function for each purpose (count vs. checking for duplicates), when it comes to zero, null and blank.

If I use COUNTIF, my formula will be like this.

=SUM(COUNTIF(A1:A9,{">0","<0"}))

Interestingly enough, null is not >0 when I use COUNTIF.

If I use =COUNTIF(A1:A9,"<>0") I get 8 instead of 6 because blanks are counted.

I am still quite confused with zero, null and blank.  I feel that I have to do different logical test(s) depending on which function I use, or if I use Boolean.  Is there an easier way (e.g. one fits all formula) to apply to all this?

Help!

Epinn

"Epinn" <someone@example.com.NO_SPAM> wrote in message news:ucbliJ7#GHA.1224@TK2MSFTNGP04.phx.gbl...
A1:A9

1,2,3,8,-9,0,null (i.e.=""),blank,8

I want to count the numbers (both positive and negative) but I want to exclude 0, null and blank.  Duplicates are counted more than once.

The formula I come up with is

=SUMPRODUCT((A1:A9<>0)*(A1:A9<>""))

It gives me the correct result of 6.  

I also play with the following formula

=SUMPRODUCT((A1:A9<>0)*(A1:A9<>"")*(A1:A9>0)+(A1:A9<0))

I do "evaluate formula" to see how 0, null, blank etc. are treated.  To my surprise, null is considered greater than 0 but blank is not.  Can someone do "evaluate formula" and tell me that I am not seeing things?

Why is null > 0?  Is it considered "text?"

Please shed some light on this.  Thanks.  

I am still having a bit of a problem coming up with a simple formula using COUNTIF or COUNT.  What do I do without you, SUMPRODUCT?

Epinn

JMB,

Thanks for the formula.  I did experiment with ISNUMBER and <>0 in the same formula, but no luck.  Why?  I used IF(COUNT( etc. and I got lost.  I changed your formula to >=0 and it picked up the blank and the null even though ISNUMBER is in the formula.  Why?  FALSE = 0.  

=COUNTIF(A1:A9,">0") gives me 5.
=A1>0 and copied down in column B.  I have got 6 TRUE.

This proves that I am not seeing things with evaluate formula.  NULL is treated as >0.

Looks like SUMPRODUCT is the one function I am willing to trust.

Epinn

This seemed to work
=SUM(--(IF(ISNUMBER(A1:A8),A1:A8,)<>0))
Array entered

"Epinn" wrote:

I like that.  So simple.  Wish we could have something even shorter.

When we have COUNT and COUNTA, we shoud be provided with COUNTIF and COUNTAIF.  Then I don't have to do any subtraction.  Don't think this happens in V. 2007.

Please tell me if you'll use the SUMPRODUCT formula or this COUNT/COUNTIF formula for your work.  My preference is SUMPRODUCT for me.

Thanks, Biff.

Epinn

=COUNT(A1:A9)-COUNTIF(A1:A9,0)

Biff

A1:A9

1,2,3,8,-9,0,null (i.e.=""),blank,8

I want to count the numbers (both positive and negative) but I want to
exclude 0, null and blank.  Duplicates are counted more than once.

The formula I come up with is

=SUMPRODUCT((A1:A9<>0)*(A1:A9<>""))

It gives me the correct result of 6.

I also play with the following formula

=SUMPRODUCT((A1:A9<>0)*(A1:A9<>"")*(A1:A9>0)+(A1:A9<0))

I do "evaluate formula" to see how 0, null, blank etc. are treated.  To my
surprise, null is considered greater than 0 but blank is not.  Can someone
do "evaluate formula" and tell me that I am not seeing things?

Why is null > 0?  Is it considered "text?"

Please shed some light on this.  Thanks.

I am still having a bit of a problem coming up with a simple formula using
COUNTIF or COUNT.  What do I do without you, SUMPRODUCT?

Epinn