Using COUNTIF to search for existence

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

Lori,

Thanks for your post. I enjoy details.

=COUNTBLANK(A:A) counts all blanks and null strings (="") in column

Agree. Same as my previous experiment. Result: 65536.

=COUNTIF(A:A,"") counts blanks and null strings (="") up to last
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

"Lori" wrote in message oups.com...
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

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.

I may be entirely wrong here......

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

"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

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

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

I may be entirely wrong here......

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

"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

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 wrote:

Lori,

Thanks for your post. I enjoy details.

=COUNTBLANK(A:A) counts all blanks and null strings (="") in column

Agree. Same as my previous experiment. Result: 65536.

=COUNTIF(A:A,"") counts blanks and null strings (="") up to last
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

"Lori" wrote in message oups.com...
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

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

"Lori" wrote in message oups.com...
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 wrote:

Lori,

Thanks for your post. I enjoy details.

=COUNTBLANK(A:A) counts all blanks and null strings (="") in column

Agree. Same as my previous experiment. Result: 65536.

=COUNTIF(A:A,"") counts blanks and null strings (="") up to last
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

"Lori" wrote in message oups.com...
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

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

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

No. I get FALSE.

Alan Beban

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

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

"Alan Beban" unavailable wrote in message ...
Epinn wrote:
. . . . Now, key in ="" to A1. Do you get TRUE? . . .

No. I get FALSE.

Alan Beban