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

I will always use the simplest formula I can come up with at the time! A lot
of times I'll write a formula and 10 minutes later I'll have thought of a
better way to do it.

Biff

"Epinn" wrote in message
...
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

"Biff" wrote in message
...
=COUNT(A1:A9)-COUNTIF(A1:A9,0)

Biff

"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