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

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

Correction

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

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.

Epinn

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

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

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

=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

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

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

Yes. ="" is an empty TEXT string. A TEXT value will always evaluate to be
greater than ANY number.

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

=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

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

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