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

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

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

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 =.

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 =.