Count unique if text

  • Thread starter Thread starter Mike Fogleman
  • Start date Start date
M

Mike Fogleman

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
 
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
 
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
 
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))

Biff
 
It's working now. Somehow I had 2 instances of Excel open and everything was
nuts.
Thanks for your efforts...
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
 
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
 
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))
Click to expand...

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


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
 
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
Click to expand...

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


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
 
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 said:
I just want to share with you what I have found.
Click to expand...

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
Click to expand...

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


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
 
The numbers are in fact numbers - the result of a CoumtA. The entire range
is format General.

Mike F

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


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
 
The entire range in column B is format General, the numbers are formula
results of COUNTA, so they are numbers.

Mike F

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


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
 
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
 
Mike,

Sorry Mike, I worry too much.

Biff,

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

Epinn

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

Mike F

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


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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

COUNTA with IF in an array 4
how to count unique entries with multiple condition 6
Identifying the last visit 2
Dave Peterson...HELP!!!! 2
count if matching text 6
Resync problem 9
Scrabble Value calculation for Welsh words 0
Combining worksheet data 0

Back
Top