Using COUNTIF to search for existence

[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

 

[Bob Phillips]

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]

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]

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]

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]

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, you might
want to see if ArrayCountIf, rather than COUNTIF, gives you the results
you seek.

Alan Beban

 

[Roger Govier]

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]

Hello Roger,

I am "intrigued" and I will experiment in detail much later today.

Let me quickly tell you what I just did. I started a blank sheet. I did =COUNTBLANK(A1:A10) and I got 10. Then I keyed in = " " to A1 and A2 respectively, and I got 8. Then I keyed in 999 into A3 and I got 7. Then I cleared A3 and I got 8. I have always wondered if Excel can tell a blank cell (never touched) from a cell that has data previously but cleared. This test tells me "no" but one expert says it can. Is my test not set up properly or have I misunderstood the expert or ......

Can you or someone tell me if Excel knows the difference between a blank cell and a cleared cell? I am pretty sure now that Excel can differentiate between a null cell and a blank cell.

Epinn (getting too detailed?)

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]

It is not difficult to differentiate between the pair (0 and blank) and null. The problem is to differentiate between 0 and blank.

As stated in my previous posts, I couldn't get TRUE when there was a match for a blank cell. If a zero was found, I got TRUE when in fact the criterion was a blank cell.

More later ......

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]

I better clarify before anyone "flames" at me. In my test, I had a space between the quotes i.e. " " and I shouldn't have.

I know why. I did evaluate formula and H1 (a blank cell) shows 0.

Epinn

Hello Roger,

I am "intrigued" and I will experiment in detail much later today.

Let me quickly tell you what I just did. I started a blank sheet. I did =COUNTBLANK(A1:A10) and I got 10. Then I keyed in = " " to A1 and A2 respectively, and I got 8. Then I keyed in 999 into A3 and I got 7. Then I cleared A3 and I got 8. I have always wondered if Excel can tell a blank cell (never touched) from a cell that has data previously but cleared. This test tells me "no" but one expert says it can. Is my test not set up properly or have I misunderstood the expert or ......

Can you or someone tell me if Excel knows the difference between a blank cell and a cleared cell? I am pretty sure now that Excel can differentiate between a null cell and a blank cell.

Epinn (getting too detailed?)

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

 

[Lori]

There are subtle differences between the various syntaxes:

=COUNTBLANK(A:A) counts all blanks and null strings (="") in column
=COUNTIF(A:A,"") counts blanks and null strings (="") up to last
used row
=COUNTIF(A:A,"=") counts only true blank cells up to last used row

 

[Epinn]

Lori,

Thanks for your post. I enjoy details.

Agree. Same as my previous experiment. Result: 65536.
used row <<

Don't understand what you meant by "up to last used row." I key some text into A2006 and I get 65535. Last used row is 2006? Please explain.

=COUNTIF(A:A,"=") counts only true blank cells up to last used row

Do you know of any link that I can read about "="? I like it. However, I have the same problem with "last used row" as stated above. Please explain.

Thanks.

Epinn

There are subtle differences between the various syntaxes:

=COUNTBLANK(A:A) counts all blanks and null strings (="") in column
=COUNTIF(A:A,"") counts blanks and null strings (="") up to last
used row
=COUNTIF(A:A,"=") counts only true blank cells up to last used row

 

[Epinn]

Hi Roger,

Thank you for taking the time to get to the bottom. I did your test and it confirmed my prepost test on blank and zero. The fact that you brought up null string (i.e. empty text) and Lori mentioned true blank (i.e. ignoring null string) help me see the big picture.

No, you are absolutely correct based on our tests that there are three conditions:-

0, blank and null

and may be there is a fourth condition - true blank??

I find it interesting that blanks have such affinity.

Blanks team up with 0 and are apart from null strings - see Roger's test.
Blanks team up with null strings - COUNTBLANK.
Blanks are true blanks and apart from null strings - COUNTIF with "=" as criterion.

Have I left out anything?

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]

Forgot to discuss about ISBLANK which is equivalent to COUNTIF with criterion "=" > 0.

ISBLANK returns TRUE for a blank and FALSE for a null string. It is different from COUNTBLANK.

Epinn

Hi Roger,

Thank you for taking the time to get to the bottom. I did your test and it confirmed my prepost test on blank and zero. The fact that you brought up null string (i.e. empty text) and Lori mentioned true blank (i.e. ignoring null string) help me see the big picture.

No, you are absolutely correct based on our tests that there are three conditions:-

0, blank and null

and may be there is a fourth condition - true blank??

I find it interesting that blanks have such affinity.

Blanks team up with 0 and are apart from null strings - see Roger's test.
Blanks team up with null strings - COUNTBLANK.
Blanks are true blanks and apart from null strings - COUNTIF with "=" as criterion.

Have I left out anything?

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

 

[Lori]

Hi Epinn - If you press Ctrl+End it will take you to the last used
cell, (equivalent to Edit > Goto > Special > Last Cell). COUNTIF
formulas on this version (XL2002 SP3) only count data in the range up
to the last cell, maybe you have a later version?

Regarding the last formula, preceding text by "=" in the criteria
generally has no effect but if an "=" sign is the only criteria only
blanks are included (you could think of an invisible blank following
the = sign). Similarly with "<>" as criteria only non blank cells
would be counted.

Lori

 

[Epinn]

Thanks for your response. It looks like COUNTIF works differently on version 2003. I didn't know that. As I stated in my previous post, it counts beyond the last used cell.

Look forward to chatting with you again in the future.

Epinn

Hi Epinn - If you press Ctrl+End it will take you to the last used
cell, (equivalent to Edit > Goto > Special > Last Cell). COUNTIF
formulas on this version (XL2002 SP3) only count data in the range up
to the last cell, maybe you have a later version?

Regarding the last formula, preceding text by "=" in the criteria
generally has no effect but if an "=" sign is the only criteria only
blanks are included (you could think of an invisible blank following
the = sign). Similarly with "<>" as criteria only non blank cells
would be counted.

Lori

 

[Epinn]

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]

. . . . Now, key in ="" to A1. Do you get TRUE? . . .

No. I get FALSE.

Alan Beban

 

[Sandy Mann]

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]

Alan,

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

I am sorry I have to ask questions, because I have tried so many times and I know what I am doing and I have got TRUE. Don't know if it is temperamental. I am sure I am not seeing things.

You do key in ="" i.e. a equal sign, shift+double quotes twice, right? No space between the quotes.

The thing is I didn't just get TRUE. The "evaluate formula" showed me something that I had a hard time accepting.

By the way, thanks for the link and I shall check if I haven't gone "crazy" after this thread.

Epinn

. . . . Now, key in ="" to A1. Do you get TRUE? . . .

No. I get FALSE.

Alan Beban