Formula to count numbers

  • Thread starter Thread starter Epinn
  • Start date Start date
E

Epinn

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:A9>0)+(A1:A9<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

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:A9>0)+(A1:A9<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

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

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

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:A9>0)+(A1:A9<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.
=A1>0 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

This seemed to work
=SUM(--(IF(ISNUMBER(A1:A8),A1:A8,)<>0))
Array entered
 
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

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:A9>0)+(A1:A9<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

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:A9>0)+(A1:A9<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

=COUNT(A1:A9)-COUNTIF(A1:A9,0)

Biff

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:A9>0)+(A1:A9<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.
Click to expand...

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

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

=COUNT(A1:A9)-COUNTIF(A1:A9,0)

Biff

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:A9>0)+(A1:A9<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))
 
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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Need Countifs Formula Help 0
Maximum Number of Blank Cells between Non Blank Cells in a Range 5
SUMPRODUCT 3
Ignore blanks in multiplication formula 4
Adding up with array formula 2
Countifs with different restrictions 1
Formula to see if a cell has an entered value 3
How to get a conditional argument to check all cells in a array? 1

Back
Top