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