Column B has a mix of text and numbers. I want to count unique text values
only.
The answer to the following list would be 8.

P2-2
P2-2
P2-7
P1-3
P-HE
P2-7
6
P11-2
P12-8
P12-8
P12-1
P12-1
P11-3



Mike F

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=SUM(N(FREQUENCY(IF(ISTEXT(B1:B13),MATCH(B1:B13,B1 :B13,0)),MATCH(B1:B13,B1:B13,0))0))

Biff

"Mike Fogleman" wrote in message
...
Column B has a mix of text and numbers. I want to count unique text values
only.
The answer to the following list would be 8.

P2-2
P2-2
P2-7
P1-3
P-HE
P2-7
6
P11-2
P12-8
P12-8
P12-1
P12-1
P11-3



Mike F

Thanks for the formula, but one problem. It works if there are no blank
cells in the range, but in my column there are many blank cells and the
formula gives 0 for answer. Is there some way around this?

Mike F
"T. Valko" wrote in message
...
Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just ENTER):

=SUM(N(FREQUENCY(IF(ISTEXT(B1:B13),MATCH(B1:B13,B1 :B13,0)),MATCH(B1:B13,B1:B13,0))0))

Biff

"Mike Fogleman" wrote in message
...
Column B has a mix of text and numbers. I want to count unique text
values only.
The answer to the following list would be 8.

P2-2
P2-2
P2-7
P1-3
P-HE
P2-7
6
P11-2
P12-8
P12-8
P12-1
P12-1
P11-3



Mike F

Oh, one more issue, and this is probably the kicker to your formula. All the
text are hyperlinks, so what is in the cell is the display value of the
hyperlink. it looks like this if the news reader will display correctly.

P2-2


P2-2


P2-7


P1-3


P-HE


P2-7


0
P11-2


P12-8


P12-8


P12-1


P12-1


P11-3



Mike F
"Mike Fogleman" wrote in message
...
Thanks for the formula, but one problem. It works if there are no blank
cells in the range, but in my column there are many blank cells and the
formula gives 0 for answer. Is there some way around this?

Mike F
"T. Valko" wrote in message
...
Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just ENTER):

=SUM(N(FREQUENCY(IF(ISTEXT(B1:B13),MATCH(B1:B13,B1 :B13,0)),MATCH(B1:B13,B1:B13,0))0))

Biff

"Mike Fogleman" wrote in message
...
Column B has a mix of text and numbers. I want to count unique text
values only.
The answer to the following list would be 8.

P2-2
P2-2
P2-7
P1-3
P-HE
P2-7
6
P11-2
P12-8
P12-8
P12-1
P12-1
P11-3



Mike F

This will account for both formula blanks ("") and empty cells (array
entered):

=SUM(N(FREQUENCY(IF((ISTEXT(B1:B20))*(B1:B20<""), MATCH(B1:B20&"",B1:B20&"",0)),MATCH(B1:B20&"",B1:B 20&"",0))0))

Biff

"Mike Fogleman" wrote in message
...
Thanks for the formula, but one problem. It works if there are no blank
cells in the range, but in my column there are many blank cells and the
formula gives 0 for answer. Is there some way around this?

Mike F
"T. Valko" wrote in message
...
Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just ENTER):

=SUM(N(FREQUENCY(IF(ISTEXT(B1:B13),MATCH(B1:B13,B1 :B13,0)),MATCH(B1:B13,B1:B13,0))0))

Biff

"Mike Fogleman" wrote in message
...
Column B has a mix of text and numbers. I want to count unique text
values only.
The answer to the following list would be 8.

P2-2
P2-2
P2-7
P1-3
P-HE
P2-7
6
P11-2
P12-8
P12-8
P12-1
P12-1
P11-3



Mike F

It's working now. Somehow I had 2 instances of Excel open and everything was
nuts.
Thanks for your efforts...
Mike F
"Mike Fogleman" wrote in message
...
Oh, one more issue, and this is probably the kicker to your formula. All
the text are hyperlinks, so what is in the cell is the display value of
the hyperlink. it looks like this if the news reader will display
correctly.

P2-2


P2-2


P2-7


P1-3


P-HE


P2-7


0
P11-2


P12-8


P12-8


P12-1


P12-1


P11-3



Mike F
"Mike Fogleman" wrote in message
...
Thanks for the formula, but one problem. It works if there are no blank
cells in the range, but in my column there are many blank cells and the
formula gives 0 for answer. Is there some way around this?

Mike F
"T. Valko" wrote in message
...
Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just ENTER):

=SUM(N(FREQUENCY(IF(ISTEXT(B1:B13),MATCH(B1:B13,B1 :B13,0)),MATCH(B1:B13,B1:B13,0))0))

Biff

"Mike Fogleman" wrote in message
...
Column B has a mix of text and numbers. I want to count unique text
values only.
The answer to the following list would be 8.

P2-2
P2-2
P2-7
P1-3
P-HE
P2-7
6
P11-2
P12-8
P12-8
P12-1
P12-1
P11-3



Mike F

OK, now I find that the formula results are 1 too many. I hand counted,
twice, and got 55 and the formula result is 56. Could that be coming from
the Frequency function? (The data array having 1 more element than the bin
array.)

Mike F
"Mike Fogleman" wrote in message
...
It's working now. Somehow I had 2 instances of Excel open and everything
was nuts.
Thanks for your efforts...
Mike F
"Mike Fogleman" wrote in message
...
Oh, one more issue, and this is probably the kicker to your formula. All
the text are hyperlinks, so what is in the cell is the display value of
the hyperlink. it looks like this if the news reader will display
correctly.

P2-2


P2-2


P2-7


P1-3


P-HE


P2-7


0
P11-2


P12-8


P12-8


P12-1


P12-1


P11-3



Mike F
"Mike Fogleman" wrote in message
...
Thanks for the formula, but one problem. It works if there are no blank
cells in the range, but in my column there are many blank cells and the
formula gives 0 for answer. Is there some way around this?

Mike F
"T. Valko" wrote in message
...
Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just ENTER):

=SUM(N(FREQUENCY(IF(ISTEXT(B1:B13),MATCH(B1:B13,B1 :B13,0)),MATCH(B1:B13,B1:B13,0))0))

Biff

"Mike Fogleman" wrote in message
...
Column B has a mix of text and numbers. I want to count unique text
values only.
The answer to the following list would be 8.

P2-2
P2-2
P2-7
P1-3
P-HE
P2-7
6
P11-2
P12-8
P12-8
P12-1
P12-1
P11-3



Mike F

The last bin evaluates to 0 so it's not a factor.

I don't know what to tell you. The formula works.

Maybe there is an instance of something like this:

P2-2
P2-2<space

They look like dupes but are unique.

Biff

"Mike Fogleman" wrote in message
...
OK, now I find that the formula results are 1 too many. I hand counted,
twice, and got 55 and the formula result is 56. Could that be coming from
the Frequency function? (The data array having 1 more element than the bin
array.)

Mike F
"Mike Fogleman" wrote in message
...
It's working now. Somehow I had 2 instances of Excel open and everything
was nuts.
Thanks for your efforts...
Mike F
"Mike Fogleman" wrote in message
...
Oh, one more issue, and this is probably the kicker to your formula. All
the text are hyperlinks, so what is in the cell is the display value of
the hyperlink. it looks like this if the news reader will display
correctly.

P2-2


P2-2


P2-7


P1-3


P-HE


P2-7


0
P11-2


P12-8


P12-8


P12-1


P12-1


P11-3



Mike F
"Mike Fogleman" wrote in message
...
Thanks for the formula, but one problem. It works if there are no blank
cells in the range, but in my column there are many blank cells and the
formula gives 0 for answer. Is there some way around this?

Mike F
"T. Valko" wrote in message
...
Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just ENTER):

=SUM(N(FREQUENCY(IF(ISTEXT(B1:B13),MATCH(B1:B13,B1 :B13,0)),MATCH(B1:B13,B1:B13,0))0))

Biff

"Mike Fogleman" wrote in message
...
Column B has a mix of text and numbers. I want to count unique text
values only.
The answer to the following list would be 8.

P2-2
P2-2
P2-7
P1-3
P-HE
P2-7
6
P11-2
P12-8
P12-8
P12-1
P12-1
P11-3



Mike F

T. Valko wrote...
This will account for both formula blanks ("") and empty cells (array
entered):

=SUM(N(FREQUENCY(IF((ISTEXT(B1:B20))*(B1:B20<"") ,MATCH(B1:B20&"",B1:B20&"",0)),
MATCH(B1:B20&"",B1:B20&"",0))0))

Or one could use

=SUMPRODUCT(ISTEXT(rng)*(rng<"")/COUNTIF(rng,rng&""))

as long as none of the values in rng contained wildcard characters *
and ? or began with =, <, , <, <= or =.

Why didn't I think of that? <g

The formula I suggested is my "standard" uniques with criteria formula.

Biff

"Harlan Grove" wrote in message
oups.com...
T. Valko wrote...
This will account for both formula blanks ("") and empty cells (array
entered):

=SUM(N(FREQUENCY(IF((ISTEXT(B1:B20))*(B1:B20<"" ),MATCH(B1:B20&"",B1:B20&"",0)),
MATCH(B1:B20&"",B1:B20&"",0))0))

Or one could use

=SUMPRODUCT(ISTEXT(rng)*(rng<"")/COUNTIF(rng,rng&""))

as long as none of the values in rng contained wildcard characters *
and ? or began with =, <, , <, <= or =.

...... the formula results are 1 too many <<

Let me share with you my test. I know you use a different data set than the one listed.

Using the data set you posted, sometimes I get 9 and sometimes I get 8. The "culprit" is 6 in row 7. It all depends on how it is entered.

If I format a blank cell (never used) as text then key in 6, I will see the green triangle (V. 2003) and ISTEXT is TRUE. Result = 9.

If I key in 6 to a blank cell (general format), then format as text, ISTEXT is FALSE. Result = 8.
This is not new to me and I know that if I rekey 6 and hit enter, ISTEXT will be TRUE.

When I read the request (first post), I wasn't sure how to interpret row 7 of the data set. 6 is a number but it is left aligned. So, I interpreted it as text and didn't want to test for ISTEXT.

Finally, I came up with the following array formula (Ctrl+Shift+Enter) which should ignore numbers regardless of how they are entered. The result is 8 for both of the above cases.

{=SUM(IF(ISERROR(VALUE(B1:B13)),(B1:B13<"")/COUNTIF(B1:B13,B1:B13&"")))}

If your numbers are in general format and/or number format, then ISTEXT will be fine.

Mind you, I am no expert. I just want to share with you what I have found.

Epinn


"Mike Fogleman" wrote in message ...
OK, now I find that the formula results are 1 too many. I hand counted,
twice, and got 55 and the formula result is 56. Could that be coming from
the Frequency function? (The data array having 1 more element than the bin
array.)

Mike F
"Mike Fogleman" wrote in message
...
It's working now. Somehow I had 2 instances of Excel open and everything
was nuts.
Thanks for your efforts...
Mike F
"Mike Fogleman" wrote in message
...
Oh, one more issue, and this is probably the kicker to your formula. All
the text are hyperlinks, so what is in the cell is the display value of
the hyperlink. it looks like this if the news reader will display
correctly.

P2-2


P2-2


P2-7


P1-3


P-HE


P2-7


0
P11-2


P12-8


P12-8


P12-1


P12-1


P11-3



Mike F
"Mike Fogleman" wrote in message
...
Thanks for the formula, but one problem. It works if there are no blank
cells in the range, but in my column there are many blank cells and the
formula gives 0 for answer. Is there some way around this?

Mike F
"T. Valko" wrote in message
...
Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just ENTER):

=SUM(N(FREQUENCY(IF(ISTEXT(B1:B13),MATCH(B1:B13,B1 :B13,0)),MATCH(B1:B13,B1:B13,0))0))

Biff

"Mike Fogleman" wrote in message
...
Column B has a mix of text and numbers. I want to count unique text
values only.
The answer to the following list would be 8.

P2-2
P2-2
P2-7
P1-3
P-HE
P2-7
6
P11-2
P12-8
P12-8
P12-1
P12-1
P11-3



Mike F

I just want to share with you what I have found.

That's a good point, numbers formatted as TEXT.

=SUM(IF(ISERROR(VALUE(B1:B13)),(B1:B13<"")/COUNTIF(B1:B13,B1:B13&"")))

=SUM(IF(ISERROR(B1:B13+0),(B1:B13<"")/COUNTIF(B1:B13,B1:B13&"")))

Biff

"Epinn" wrote in message
...
...... the formula results are 1 too many <<

Let me share with you my test. I know you use a different data set than the
one listed.

Using the data set you posted, sometimes I get 9 and sometimes I get 8. The
"culprit" is 6 in row 7. It all depends on how it is entered.

If I format a blank cell (never used) as text then key in 6, I will see the
green triangle (V. 2003) and ISTEXT is TRUE. Result = 9.

If I key in 6 to a blank cell (general format), then format as text, ISTEXT
is FALSE. Result = 8.
This is not new to me and I know that if I rekey 6 and hit enter, ISTEXT
will be TRUE.

When I read the request (first post), I wasn't sure how to interpret row 7
of the data set. 6 is a number but it is left aligned. So, I interpreted
it as text and didn't want to test for ISTEXT.

Finally, I came up with the following array formula (Ctrl+Shift+Enter) which
should ignore numbers regardless of how they are entered. The result is 8
for both of the above cases.

{=SUM(IF(ISERROR(VALUE(B1:B13)),(B1:B13<"")/COUNTIF(B1:B13,B1:B13&"")))}

If your numbers are in general format and/or number format, then ISTEXT will
be fine.

Mind you, I am no expert. I just want to share with you what I have found.

Epinn


"Mike Fogleman" wrote in message
...
OK, now I find that the formula results are 1 too many. I hand counted,
twice, and got 55 and the formula result is 56. Could that be coming from
the Frequency function? (The data array having 1 more element than the bin
array.)

Mike F
"Mike Fogleman" wrote in message
...
It's working now. Somehow I had 2 instances of Excel open and everything
was nuts.
Thanks for your efforts...
Mike F
"Mike Fogleman" wrote in message
...
Oh, one more issue, and this is probably the kicker to your formula. All
the text are hyperlinks, so what is in the cell is the display value of
the hyperlink. it looks like this if the news reader will display
correctly.

P2-2


P2-2


P2-7


P1-3


P-HE


P2-7


0
P11-2


P12-8


P12-8


P12-1


P12-1


P11-3



Mike F
"Mike Fogleman" wrote in message
...
Thanks for the formula, but one problem. It works if there are no blank
cells in the range, but in my column there are many blank cells and the
formula gives 0 for answer. Is there some way around this?

Mike F
"T. Valko" wrote in message
...
Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just ENTER):

=SUM(N(FREQUENCY(IF(ISTEXT(B1:B13),MATCH(B1:B13,B1 :B13,0)),MATCH(B1:B13,B1:B13,0))0))

Biff

"Mike Fogleman" wrote in message
...
Column B has a mix of text and numbers. I want to count unique text
values only.
The answer to the following list would be 8.

P2-2
P2-2
P2-7
P1-3
P-HE
P2-7
6
P11-2
P12-8
P12-8
P12-1
P12-1
P11-3



Mike F

Thanks, Biff. Why didn't I think of +0? Still not getting used to Boolean.

Since I am a fan of SUMPRODUCT, I'll throw in this.

=SUMPRODUCT(ISERROR(B1:B13+0)*(B1:B13<"")/COUNTIF(B1:B13,B1:B13&""))

Wonder if it is more efficient than array SUM(IF( ......

Epinn

"T. Valko" wrote in message ...
I just want to share with you what I have found.

That's a good point, numbers formatted as TEXT.

=SUM(IF(ISERROR(VALUE(B1:B13)),(B1:B13<"")/COUNTIF(B1:B13,B1:B13&"")))

=SUM(IF(ISERROR(B1:B13+0),(B1:B13<"")/COUNTIF(B1:B13,B1:B13&"")))

Biff

"Epinn" wrote in message
...
...... the formula results are 1 too many <<

Let me share with you my test. I know you use a different data set than the
one listed.

Using the data set you posted, sometimes I get 9 and sometimes I get 8. The
"culprit" is 6 in row 7. It all depends on how it is entered.

If I format a blank cell (never used) as text then key in 6, I will see the
green triangle (V. 2003) and ISTEXT is TRUE. Result = 9.

If I key in 6 to a blank cell (general format), then format as text, ISTEXT
is FALSE. Result = 8.
This is not new to me and I know that if I rekey 6 and hit enter, ISTEXT
will be TRUE.

When I read the request (first post), I wasn't sure how to interpret row 7
of the data set. 6 is a number but it is left aligned. So, I interpreted
it as text and didn't want to test for ISTEXT.

Finally, I came up with the following array formula (Ctrl+Shift+Enter) which
should ignore numbers regardless of how they are entered. The result is 8
for both of the above cases.

{=SUM(IF(ISERROR(VALUE(B1:B13)),(B1:B13<"")/COUNTIF(B1:B13,B1:B13&"")))}

If your numbers are in general format and/or number format, then ISTEXT will
be fine.

Mind you, I am no expert. I just want to share with you what I have found.

Epinn


"Mike Fogleman" wrote in message
...
OK, now I find that the formula results are 1 too many. I hand counted,
twice, and got 55 and the formula result is 56. Could that be coming from
the Frequency function? (The data array having 1 more element than the bin
array.)

Mike F
"Mike Fogleman" wrote in message
...
It's working now. Somehow I had 2 instances of Excel open and everything
was nuts.
Thanks for your efforts...
Mike F
"Mike Fogleman" wrote in message
...
Oh, one more issue, and this is probably the kicker to your formula. All
the text are hyperlinks, so what is in the cell is the display value of
the hyperlink. it looks like this if the news reader will display
correctly.

P2-2


P2-2


P2-7


P1-3


P-HE


P2-7


0
P11-2


P12-8


P12-8


P12-1


P12-1


P11-3



Mike F
"Mike Fogleman" wrote in message
...
Thanks for the formula, but one problem. It works if there are no blank
cells in the range, but in my column there are many blank cells and the
formula gives 0 for answer. Is there some way around this?

Mike F
"T. Valko" wrote in message
...
Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just ENTER):

=SUM(N(FREQUENCY(IF(ISTEXT(B1:B13),MATCH(B1:B13,B1 :B13,0)),MATCH(B1:B13,B1:B13,0))0))

Biff

"Mike Fogleman" wrote in message
...
Column B has a mix of text and numbers. I want to count unique text
values only.
The answer to the following list would be 8.

P2-2
P2-2
P2-7
P1-3
P-HE
P2-7
6
P11-2
P12-8
P12-8
P12-1
P12-1
P11-3



Mike F

The numbers are in fact numbers - the result of a CoumtA. The entire range
is format General.

Mike F
"Epinn" wrote in message
...
...... the formula results are 1 too many <<

Let me share with you my test. I know you use a different data set than the
one listed.

Using the data set you posted, sometimes I get 9 and sometimes I get 8. The
"culprit" is 6 in row 7. It all depends on how it is entered.

If I format a blank cell (never used) as text then key in 6, I will see the
green triangle (V. 2003) and ISTEXT is TRUE. Result = 9.

If I key in 6 to a blank cell (general format), then format as text, ISTEXT
is FALSE. Result = 8.
This is not new to me and I know that if I rekey 6 and hit enter, ISTEXT
will be TRUE.

When I read the request (first post), I wasn't sure how to interpret row 7
of the data set. 6 is a number but it is left aligned. So, I interpreted
it as text and didn't want to test for ISTEXT.

Finally, I came up with the following array formula (Ctrl+Shift+Enter) which
should ignore numbers regardless of how they are entered. The result is 8
for both of the above cases.

{=SUM(IF(ISERROR(VALUE(B1:B13)),(B1:B13<"")/COUNTIF(B1:B13,B1:B13&"")))}

If your numbers are in general format and/or number format, then ISTEXT will
be fine.

Mind you, I am no expert. I just want to share with you what I have found.

Epinn


"Mike Fogleman" wrote in message
...
OK, now I find that the formula results are 1 too many. I hand counted,
twice, and got 55 and the formula result is 56. Could that be coming from
the Frequency function? (The data array having 1 more element than the bin
array.)

Mike F
"Mike Fogleman" wrote in message
...
It's working now. Somehow I had 2 instances of Excel open and everything
was nuts.
Thanks for your efforts...
Mike F
"Mike Fogleman" wrote in message
...
Oh, one more issue, and this is probably the kicker to your formula. All
the text are hyperlinks, so what is in the cell is the display value of
the hyperlink. it looks like this if the news reader will display
correctly.

P2-2


P2-2


P2-7


P1-3


P-HE


P2-7


0
P11-2


P12-8


P12-8


P12-1


P12-1


P11-3



Mike F
"Mike Fogleman" wrote in message
...
Thanks for the formula, but one problem. It works if there are no blank
cells in the range, but in my column there are many blank cells and the
formula gives 0 for answer. Is there some way around this?

Mike F
"T. Valko" wrote in message
...
Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just ENTER):

=SUM(N(FREQUENCY(IF(ISTEXT(B1:B13),MATCH(B1:B13,B1 :B13,0)),MATCH(B1:B13,B1:B13,0))0))

Biff

"Mike Fogleman" wrote in message
...
Column B has a mix of text and numbers. I want to count unique text
values only.
The answer to the following list would be 8.

P2-2
P2-2
P2-7
P1-3
P-HE
P2-7
6
P11-2
P12-8
P12-8
P12-1
P12-1
P11-3



Mike F

The entire range in column B is format General, the numbers are formula
results of COUNTA, so they are numbers.

Mike F
"Epinn" wrote in message
...
...... the formula results are 1 too many <<

Let me share with you my test. I know you use a different data set than the
one listed.

Using the data set you posted, sometimes I get 9 and sometimes I get 8. The
"culprit" is 6 in row 7. It all depends on how it is entered.

If I format a blank cell (never used) as text then key in 6, I will see the
green triangle (V. 2003) and ISTEXT is TRUE. Result = 9.

If I key in 6 to a blank cell (general format), then format as text, ISTEXT
is FALSE. Result = 8.
This is not new to me and I know that if I rekey 6 and hit enter, ISTEXT
will be TRUE.

When I read the request (first post), I wasn't sure how to interpret row 7
of the data set. 6 is a number but it is left aligned. So, I interpreted
it as text and didn't want to test for ISTEXT.

Finally, I came up with the following array formula (Ctrl+Shift+Enter) which
should ignore numbers regardless of how they are entered. The result is 8
for both of the above cases.

{=SUM(IF(ISERROR(VALUE(B1:B13)),(B1:B13<"")/COUNTIF(B1:B13,B1:B13&"")))}

If your numbers are in general format and/or number format, then ISTEXT will
be fine.

Mind you, I am no expert. I just want to share with you what I have found.

Epinn


"Mike Fogleman" wrote in message
...
OK, now I find that the formula results are 1 too many. I hand counted,
twice, and got 55 and the formula result is 56. Could that be coming from
the Frequency function? (The data array having 1 more element than the bin
array.)

Mike F
"Mike Fogleman" wrote in message
...
It's working now. Somehow I had 2 instances of Excel open and everything
was nuts.
Thanks for your efforts...
Mike F
"Mike Fogleman" wrote in message
...
Oh, one more issue, and this is probably the kicker to your formula. All
the text are hyperlinks, so what is in the cell is the display value of
the hyperlink. it looks like this if the news reader will display
correctly.

P2-2


P2-2


P2-7


P1-3


P-HE


P2-7


0
P11-2


P12-8


P12-8


P12-1


P12-1


P11-3



Mike F
"Mike Fogleman" wrote in message
...
Thanks for the formula, but one problem. It works if there are no blank
cells in the range, but in my column there are many blank cells and the
formula gives 0 for answer. Is there some way around this?

Mike F
"T. Valko" wrote in message
...
Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just ENTER):

=SUM(N(FREQUENCY(IF(ISTEXT(B1:B13),MATCH(B1:B13,B1 :B13,0)),MATCH(B1:B13,B1:B13,0))0))

Biff

"Mike Fogleman" wrote in message
...
Column B has a mix of text and numbers. I want to count unique text
values only.
The answer to the following list would be 8.

P2-2
P2-2
P2-7
P1-3
P-HE
P2-7
6
P11-2
P12-8
P12-8
P12-1
P12-1
P11-3



Mike F

Thanks Harlan, your formula also works. Both formula results are identical
and match my re-re-re-count by hand. The Frequency formula worked fine on 2
of my 3 worksheets, but erred with #N/A on the original sheet. The others
are copies of it with different data. Strange since it was the parent
template from which the others were evolved. Harlan's formula works on all
sheets and strangely enough, whether it is array entered or not.

Thanks guys for all your input
Mike F
"Harlan Grove" wrote in message
oups.com...
T. Valko wrote...
This will account for both formula blanks ("") and empty cells (array
entered):

=SUM(N(FREQUENCY(IF((ISTEXT(B1:B20))*(B1:B20<"" ),MATCH(B1:B20&"",B1:B20&"",0)),
MATCH(B1:B20&"",B1:B20&"",0))0))

Or one could use

=SUMPRODUCT(ISTEXT(rng)*(rng<"")/COUNTIF(rng,rng&""))

as long as none of the values in rng contained wildcard characters *
and ? or began with =, <, , <, <= or =.

Mike,

Sorry Mike, I worry too much.

Biff,

That's a good point ...... <<

Thank you for the encouragement. You are a good teacher and are always kind to me. I appreciate it.

Epinn

"Mike Fogleman" wrote in message ...
The entire range in column B is format General, the numbers are formula
results of COUNTA, so they are numbers.

Mike F
"Epinn" wrote in message
...
...... the formula results are 1 too many <<

Let me share with you my test. I know you use a different data set than the
one listed.

Using the data set you posted, sometimes I get 9 and sometimes I get 8. The
"culprit" is 6 in row 7. It all depends on how it is entered.

If I format a blank cell (never used) as text then key in 6, I will see the
green triangle (V. 2003) and ISTEXT is TRUE. Result = 9.

If I key in 6 to a blank cell (general format), then format as text, ISTEXT
is FALSE. Result = 8.
This is not new to me and I know that if I rekey 6 and hit enter, ISTEXT
will be TRUE.

When I read the request (first post), I wasn't sure how to interpret row 7
of the data set. 6 is a number but it is left aligned. So, I interpreted
it as text and didn't want to test for ISTEXT.

Finally, I came up with the following array formula (Ctrl+Shift+Enter) which
should ignore numbers regardless of how they are entered. The result is 8
for both of the above cases.

{=SUM(IF(ISERROR(VALUE(B1:B13)),(B1:B13<"")/COUNTIF(B1:B13,B1:B13&"")))}

If your numbers are in general format and/or number format, then ISTEXT will
be fine.

Mind you, I am no expert. I just want to share with you what I have found.

Epinn


"Mike Fogleman" wrote in message
...
OK, now I find that the formula results are 1 too many. I hand counted,
twice, and got 55 and the formula result is 56. Could that be coming from
the Frequency function? (The data array having 1 more element than the bin
array.)

Mike F
"Mike Fogleman" wrote in message
...
It's working now. Somehow I had 2 instances of Excel open and everything
was nuts.
Thanks for your efforts...
Mike F
"Mike Fogleman" wrote in message
...
Oh, one more issue, and this is probably the kicker to your formula. All
the text are hyperlinks, so what is in the cell is the display value of
the hyperlink. it looks like this if the news reader will display
correctly.

P2-2


P2-2


P2-7


P1-3


P-HE


P2-7


0
P11-2


P12-8


P12-8


P12-1


P12-1


P11-3



Mike F
"Mike Fogleman" wrote in message
...
Thanks for the formula, but one problem. It works if there are no blank
cells in the range, but in my column there are many blank cells and the
formula gives 0 for answer. Is there some way around this?

Mike F
"T. Valko" wrote in message
...
Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just ENTER):

=SUM(N(FREQUENCY(IF(ISTEXT(B1:B13),MATCH(B1:B13,B1 :B13,0)),MATCH(B1:B13,B1:B13,0))0))

Biff

"Mike Fogleman" wrote in message
...
Column B has a mix of text and numbers. I want to count unique text
values only.
The answer to the following list would be 8.

P2-2
P2-2
P2-7
P1-3
P-HE
P2-7
6
P11-2
P12-8
P12-8
P12-1
P12-1
P11-3



Mike F