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" wrote in message ...
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:A90)+(A1:A 9<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