Using COUNTIF to search for existence

[Epinn]

Sandy,

Before I forget, did you get FALSE or TRUE with *my* experiment? Alan got FALSE and I got TRUE. Please tell me what you have got and what version, please.

You have an interesting test there and I am glad that I am not seeing things. However, I am totally lost and on the verge of going ...... You know, Roger and I spent some time doing tests to come to an agreement that blank is equivalent to 0 when we use COUNTIF and A1 (a blank cell) as criterion. Now your test which says

"" = Blank but Blank <> ""

may have complicated our "understanding." Of course, you are not to blame.

One thing I am quite sure though - BLANK doesn't have a backbone and can have great affinity depending on its mood.

My dear Excel, do you have any idea what I am going through? Any MS Excel developer(s) reading this?

Epinn

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


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

Hi Epinn

i may be entirely wrong here, and I am quite willing to be shot down in
flames<bg>
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



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

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

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.

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

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

Epinn

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)

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

 

[Alan Beban]

Alan,

Which version do you use? When you got FALSE did you do evaluate formula? Probably no point.

My bad! I had 10 in Cell A10 rather than 0. I get TRUE (xl2002)

With the empty string in A1 and A2 blank, the formula sees two empty
strings (A1 and A2) but only 1 blank (A2). If you change the empty
string in A1 to a 0, it sees 2 0's (A1 and A10) and 2 blanks (A2 and ?).
If you then change the blank in A2 to an empty string it sees 2 0's (A1
and A10) and 1 empty string (A2).

Alan Beban

 

[Alan Beban]

As I suggested previously, the ArrayCountIf function in the freely
downloadable file at http://home.pacbell.net/beban seems to evaluate
0's, blanks and empty strings separately. I had to use
ArrayCountIf(A$1:A$10,A1) and copy down, however, because the function
doesn't currently accept multiple sought values, which is what it
considers the array formula as involving.

Alan Beban

 

[Pete_UK]

Epinn,

I'm not sure how far you want to go with this investigation, but it
strikes me that there are some other "blanks" that you have not
reported on:

Some people will clear the contents of a cell by pressing the space
bar. The cell looks empty, as does the formula bar, and even in edit
mode it is difficult to "see" the space, so a normal user might
consider these cells to be "blank". Obviously, multiple spaces will
have the same effect.

The non-breaking space character, 160, will have the same appearance,
as will combinations of this with "normal" space characters.

If you have formulae which return a null string, "", and then fix the
values using paste special, Excel will leave a single apostrophe in the
cell, which will look empty. Obviously, this could also occur with
spaces and nb spaces after it.

Some character codes (eg 10) do not give rise to printable characters,
and you may have some of these in cells which make them seem blank.

All of these will appear empty, but clearly are not. You might want to
look into ways of handling these situations.

Pete

 

[Roger Govier]

Hi Epinn

Whilst I predominantly use Excel 2003, I have Excel 97, 2000, 2002 and
2003 loaded on my machine for working with and testing different
client's data against workbooks I have created for them with both
functions and VBA.

I have loaded each version and tested the following and I can discern no
difference in the behaviour between all versions with respect to the
various tests.

Sandy's assertion
"" = Blank but Blank <> ""
is true in the way that COUNTIF works.

I set up the following in cells A19
(I am using words here to describe what was included in cells A1:B9 and
actual results for C19)

Blank Blank TRUE 3
Blank Null TRUE 3
Null Blank TRUE 6
Null Null TRUE 6
Blank Zero TRUE 3
Zero Blank TRUE 3
Zero Zero TRUE 3
Zero Null FALSE 3
Null Zero FALSE 6


In cell C1 I have the formula =A1=B1 copied down through C2:C9
In cell D1 I have the formula =COUNTIF($A$1:$A$9,A1) copied down
through D29

(Using Sandy's array entered formula =COUNTIF(A1:A9,A1:A9) through cells
E1:E9 produces identical counts so has not been included in the table
above)

In the logic tests for the 9 possible comparisons of Zero, Blank and
Null we see that all comparisons are True other than Zero compared with
Null.

Countif, however, correctly counts that there are 3 Zeros in the range,
and 3 Blank's in the range.
But it counts 6 Nulls (being the 3 real Nulls' plus the 3 Blank's)

So Countif when starting with Blank as the comparative, ignores Nulls
and treats them as Not equal to Blank
When starting with Null as the comparative, it treats Blank as being
equal to Null and includes them.

So Boolean logic says Blank equals Null and Null equals Blank, but
Countif doesn't agree.
Very strange!!!

--
Regards

Roger Govier


Sandy,

Before I forget, did you get FALSE or TRUE with *my* experiment? Alan
got FALSE and I got TRUE. Please tell me what you have got and what
version, please.

You have an interesting test there and I am glad that I am not seeing
things. However, I am totally lost and on the verge of going ......
You know, Roger and I spent some time doing tests to come to an
agreement that blank is equivalent to 0 when we use COUNTIF and A1 (a
blank cell) as criterion. Now your test which says

"" = Blank but Blank <> ""

may have complicated our "understanding." Of course, you are not to
blame.

One thing I am quite sure though - BLANK doesn't have a backbone and can
have great affinity depending on its mood.

My dear Excel, do you have any idea what I am going through? Any MS
Excel developer(s) reading this?

Epinn

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


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

Hi Epinn

i may be entirely wrong here, and I am quite willing to be shot down in
flames<bg>
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



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

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

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.

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

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

Epinn

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)

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

 

[Epinn]

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

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


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

Hi Epinn

i may be entirely wrong here, and I am quite willing to be shot down in
flames<bg>
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



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

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

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.

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

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

Epinn

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)

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

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


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

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


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

Hi Epinn

i may be entirely wrong here, and I am quite willing to be shot down in
flames<bg>
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



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

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

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.

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

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

Epinn

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)

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

 

[Roger Govier]

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

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Roger Govier]

Sandy

unless I was so confused that I completely reversed everything.

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

 

[Epinn]

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

unless I was so confused that I completely reversed everything.

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

 

[Epinn]

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

Epinn

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