JMB,

Firstly, please change your formula to A1:A9 for my data set so that we are talking about the same thing.

There is nothing wrong with your formula when we use 0 or <0. I get the correct result of 5 and 6 respectively.

If you reread my previous post again, you will note that I was playing with = (i.e. greater than and equal to) 0. This is where we can have a problem.

Blank and null are counted even though ISNUMBER is used. This is because FALSE is translated to 0.

Epinn

"JMB" wrote in message ...
=SUM(--(IF(ISNUMBER(A1:A8),A1:A8,)0))

gives me 4. Did you put the "," in the 3 argument of the IF function. It's
the same as:

=SUM(--(IF(ISNUMBER(A1:A8),A1:A8,0)0))



"Epinn" wrote:

JMB,

Thanks for the formula. 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.
=A10 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

"JMB" wrote in message ...
This seemed to work
=SUM(--(IF(ISNUMBER(A1:A8),A1:A8,)<0))
Array entered

"Epinn" wrote:

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