Using COUNTIF to search for existence

and I thought I had a monopoly on that particular trait!! bg

Oh, no, I'll be the first one to get confused and I also confuse others around me too. bg

Confusion is contagious. Earlier I posted a few long formulae on SUM, 1/COUNTIF etc. which can cause some confusion. By the way, I didn't create those formulae ......

Thank you all for playing along. I appreciate it.

Epinn

"Roger Govier" wrote in message ...
Sandy

unless I was so confused that I completely reversed everything.

and I thought I had a monopoly on that particular trait!! bg


--
Regards

Roger Govier


"Sandy Mann" wrote in message
...
Roger,

Yes that is what I get today - I don't know what I did unless I was so
confused that I completely reversed everything.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Roger Govier" wrote in message
...
Hi Sandy

I tried it in all versions of XL from 97 to 2003 and got
10,0,0,0,0,0,0,0,0,0
in all cases.

Windows XP Professional
Version 5.1.2600 Service Pack 2 Build 2600
Xl 97 SR-1
--
Regards

Roger Govier


"Sandy Mann" wrote in message
...
Epinn,

I am not sure that we are proving anything here but I found that
with ="" in A1 and A2:A10 left untouched I got
0,10,10,10,10,10,10,10,10,10 form an array entered
=COUNTIF(A1:A10,A1:A10) and using XL97

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Epinn" wrote in message
...
Our (Roger's and my) previous tests using COUNTIF tell us that blank
but not null string is treated as 0. My subsequent test using
SUMPRODUCT/COUNTIF tells us that null string (="") but not blank is
treated like 0. These tests contradict one another.

Then Sandy's test reveals that blank and null string have another
story between the two of them not having zero as part of the picture
this time. I have taken Sandy's test further. Using the same array
formula

=COUNTIF(A1:A10,A1:A10)

and highlighting B1 to B10, I reverse Sandy's entries for A1:A10
i.e. I leave A1 as blank and key in ="" to the rest. But the result
is not reversed accordingly. I have got 2,1,1,1,1,1,1,1,1,1.
Remember with Sandy's data set (the opposite of mine) the result is
10,0,0,0,0,0,0,0,0,0,0? What is the message this time? Can someone
say this in English?

Will digest Pete's and Roger's posts later. I shall make a point to
tie up the loose ends this weekend and *move on with my life*. When
I started this thread, I just wanted to make a comment and didn't
expect all this *endless* confusion.

Epinn

"Sandy Mann" wrote in message
...
Epinn,

Just to muddy the waters try this:

in a1 enter ="" and leave A2:A10 empty

now highlight B1+B10 and enter:

=COUNTIF(A1:A10,A1:A10)

and complete it by array entering the formula(s) with Ctrl + Shift +
Enter

I get:

10
0
0
0
0
0
0
0
0
0

Therefore to Excel "" = Blank but Blank ""

Go figure!

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Epinn" wrote in message
...
Roger,

I should stop playing with blank, null, zero etc. real soon or I may
go
"insane."

Let me tell you what I have found. Not sure if it is a bug.

Key in 1,2,3,4,5,6,7,8,9,0 to A1:A10 respectively i.e. A1 = 1, A2 =
2 etc.
etc.

Then key in the following somewhere.

=SUMPRODUCT(COUNTIF(A1:A10,A1:A10))10

You get a FALSE because there is no duplicate. Now, change the "2"
in A2 to
"1".

You get a TRUE because there is duplicate i.e. A1=A2=1. So far so
good.
Please do "evaluate formula" at this time. You will see
{2,2,1,1,.....} and
this makes sense. I just want to make note of the lst and 2nd "2".

Now clear A1 and A2 by pressing del key and you have two blank
cells. You
get a FALSE. I can accept that the formula ignore two blanks. Now
the
following confuses me.

You still have A1 and A2 as two empty blank cells, right? Now, key
in =""
to A1. Do you get TRUE? So, what is the duplicate here? Let's do
"evaluate formula" and you will see just ONE "2". I can't explain
this.
Remember earlier when we have A1 and A2 being "1" evaluate formula
shows us
{2,2,1,1,......}. It doesn't make sense to me that there is only
ONE "2"
now. If there is a duplicate, there should be another "2" somewhere
between
the { }.

Is this a bug or am I going "crazy?" By the way, I use version
2003. Would
you mind telling me if you have got the same.

Now, I am going to explore the following which is better to search
for
duplicates.

=IF(COUNTA(A1:A10),MAX(FREQUENCY(A1:A10,A1:A10))1 ,"")

I appreciate your help.

Epinn

"Roger Govier" wrote in message
...
Hi Epinn

i may be entirely wrong here, and I am quite willing to be shot down
in
flamesbg
I think there are 3 conditions
Zero in a cell 0
an Empty cell BLANK
Null in a cell =""

Blank and Zero are counted as equivalent, but are both different
from
Null.

Your test is looking at the whole of columns C through column G
Enter the following into cells A1:A7 respectively on a completely
new
empty sheet
0, BLANK, ="", ="", BLANK, 0, BLANK
In B1 enter
=(COUNTIF(C:G,A1)0) and copy down through B2:B3
in B4 enter
=COUNTIF(C:G,A4) and copy down through B5:B6
In B7 enter
=COUNTIF(C:G,A7)+COUNTIF(C:G,"")

The results you will see are
FALSE, FALSE, TRUE, 327680, 0, 0 , 327680

Now enter a 0 in say cell D1 and you will see the results change to
TRUE, TRUE, TRUE, 327679, 1, 1, 327680

So you can see that the count for a totally Blank cell is the same
as
the count for a Zero cell, but is different from the count for a
Null
cell ""

You would need to combine both cases if you want to return True for
0 or
Null as
=COUNTIF(C:G,A7)0+COUNTIF(C:G,"")0
(here the plus sign is acting equivalent to the "OR" condition)

--
Regards

Roger Govier


"Epinn" wrote in message
...
=IF(A1"",COUNTIF(C:G,A1)0)
COUNTIF(C:G,A1)0

If A1 is blank and there is a blank in C:G, I don't get TRUE with
the
second formula. That is why I say I have to use COUNTBLANK.

Have I confused everyone now?

Epinn

"Epinn" wrote in message
...
I know I can use the following formula to differentiate.

=IF(A1"",COUNTIF(C:G,A1)0)

I was just surprised that blanks and zeros are treated the same.

Epinn

"Epinn" wrote in message
...
Should have included my formula instead of writing lines. Although
I
say column, I use each cell in the column as a criterion. The
formula
is copied down.

=COUNTIF(C:G,A1)0

What I want to say is if A1 = 0 or blank, and 0 doesn't exist in
C:G, I
get FALSE. If 0 exists in C:G, I get TRUE. If A1 is a blank, I
don't
want to see TRUE.

=COUNTIF(A:A,"") counts blanks for me.

Yes, when you include "" as part of the syntax.

For my experiment, H1 is a blank and =COUNTIF(A:A,H1) returns 0.

Epinn

"Bob Phillips" wrote in message
...
You are not making sense (at least to me). What does another column
have
to
do with it, COUNTIF works on a single range.

=COUNTIF(A:A,"") counts blanks for me.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
I want to use COUNTIF 0 to determine if a value in column A exists
in
another column. This works fine for non-blank and non-zero cells.
COUNTIF
appears to handle a blank and a "0" the same way. If they exist in
column A
and a blank exists in another column, FALSE will be returned for
both
"0"
and blank. If a "0" exists in another column, TRUE will be returned
for
both "0" and blank. Wonder how I can differentiate between the two.
If
I
have a blank in column A and a "0" in another column, I don't want
to
consider it a match. Guess I have to use COUNTBLANK on the two
columns
respectively.

Any comments?

Epinn

In case anyone is interested, F5SpecialBlanks only picks up true blanks but not null strings (="").

Epinn

"Epinn" wrote in message ...
I want to use COUNTIF 0 to determine if a value in column A exists in another column. This works fine for non-blank and non-zero cells. COUNTIF appears to handle a blank and a "0" the same way. If they exist in column A and a blank exists in another column, FALSE will be returned for both "0" and blank. If a "0" exists in another column, TRUE will be returned for both "0" and blank. Wonder how I can differentiate between the two. If I have a blank in column A and a "0" in another column, I don't want to consider it a match. Guess I have to use COUNTBLANK on the two columns respectively.

Any comments?

Epinn