Re: Count unique if text

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <mikefogleman@xxxxxxxxxxxxx> wrote in message
news:e7CEewNPHHA.1248@xxxxxxxxxxxxxxxxxxxxxxx
It's working now. Somehow I had 2 instances of Excel open and everything
was nuts.
Thanks for your efforts...
Mike F
"Mike Fogleman" <mikefogleman@xxxxxxxxxxxxx> wrote in message
news:%23fu93UNPHHA.1248@xxxxxxxxxxxxxxxxxxxxxxx
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" <mikefogleman@xxxxxxxxxxxxx> wrote in message
news:%23EFV2FNPHHA.2312@xxxxxxxxxxxxxxxxxxxxxxx
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" <biffinpitt@xxxxxxxxxxx> wrote in message
news:Ocj9jSMPHHA.324@xxxxxxxxxxxxxxxxxxxxxxx
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" <mikefogleman@xxxxxxxxxxxxx> wrote in message
news:uNxLD9LPHHA.4368@xxxxxxxxxxxxxxxxxxxxxxx
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











.

Relevant Pages

  • Re: Count unique if text
    ... The last bin evaluates to 0 so it's not a factor. ... (The data array having 1 more element than the bin ... Mike F ... cells in the range, but in my column there are many blank cells and the ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Vector Table
    ... But there is no data displayed in any of the "cells". ... can be numbers or letters or puncuation or whatever. ... details of what's in the cell and how to display it. ... Just because you have an array Xdoesn't ...
    (comp.lang.cpp)
  • Re: Peremanently removing decimal places
    ... Is it possible to paste values as well for the same cells,so any fields that are still formula fields get changed to actual values? ... >> Hi Mike, ... >> a number with 2 dp willl display as 7.57 but it will still really be ... >> significant differences in price. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Count unique if text
    ... When I read the request, I wasn't sure how to interpret row 7 of the data set. ... I came up with the following array formula which should ignore numbers regardless of how they are entered. ... Mike F ... cells in the range, but in my column there are many blank cells and the ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Display Dynamic Range
    ... I still don't know how to DISPLAY the result without copying the ... The Daily_Postage array changes every day. ... Daily Postage is 12 x 500 array of cells each with a formula or ... sheet2 without spreading formulas around in each cell. ...
    (microsoft.public.excel.worksheet.functions)