"COUNTU" function in Excel to count unique entries in a range

Hi!

> In order to count the number of unique entries in a range of cells, a very
> complicated nested set of functions (SUM, IF, FREQUENCY, MATCH, LEN, etc.)
> is
> required in Excel.

Not really!

=SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&""))

Biff

"WayneL" <[email protected]> wrote in message
news:[email protected]...
> In order to count the number of unique entries in a range of cells, a very
> complicated nested set of functions (SUM, IF, FREQUENCY, MATCH, LEN, etc.)
> is
> required in Excel.
>
> I suggest adding a COUNTU worksheet function that would automatically
> count
> the number of unique data entries. It should have options for counting
> numbers, numbers + text, excluding blank cells, etc.
>
> Thanks,
> Wayne
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow
> this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
> http://www.microsoft.com/office/comm...heet.functions

Harlan Grove wrote...
....
>There comes a point when it makes no sense to customize a function any
>further. Generally, as general as possible is best. A rich set of
>criterion strings is arguably going too far. Also, there are times the
>best response to OPs is 'what you want to do could be done better . .
>.' IMO, this is a situation in which the flexibility of subexpressions
>is clearly preferable to the rigidity of elaborately specific udfs.

Nevertheless, I kept thinking about this. Being a bit of a closet
systems programmer, this is what I came up with.


Function uniqc( _
t As String, _
c As String, _
ParamArray a() As Variant _
) As Variant
'---------------------------------------------------------------
'requires Windows Scripting Runtime and Excel 2000 or higher or
'an Excel 97/VBA5.* implementation of VBA6's Replace function
'---------------------------------------------------------------
Const ALLTYPES As String = "Blank Number Date Text Error Logical"
Static d As Object, s As Long
Dim x As Variant, y As Variant, n As Long, cc As String

On Error GoTo CleanUp

If t = "" Or t = "*" Or t = "all" Then t = ALLTYPES

t = Replace(t, "Blank", "Empty", , , vbTextCompare)
t = Replace(t, "Number", "Double", , , vbTextCompare)
t = Replace(t, "Text", "String", , , vbTextCompare)
t = Replace(t, "Logical", "Boolean", , , vbTextCompare)

If s = 0 Then Set d = New Dictionary

s = s + 1

For Each x In a
If TypeOf x Is Range Then
For Each y In x.Areas
Call uniqc(t, c, y.Value)
Next y
ElseIf IsArray(x) Then
For Each y In x
Call uniqc(t, c, y)
Next y
ElseIf InStr(1, t, TypeName(x), vbTextCompare) > 0 _
And Not d.Exists(x) Then _
cc = IIf(c = "", "TRUE", Replace(c, "$$", x))
If Evaluate(cc) Then d.Add Key:=x, Item:=""
End If
Next x

s = s - 1

If s > 0 Then Exit Function

CleanUp:
x = d.Keys
d.RemoveAll
Set d = Nothing
s = 0

ReDim y(LBound(x) To UBound(x), 1 To 1)

For n = LBound(x) To UBound(y, 1)
y(n, 1) = x(n)
Next n

uniqc = y

Erase x, y
End Function


Sample usage:

=uniqc("number","mod($$,2)=1",MOD(ROW(INDIRECT("1:20")),10))

which returns {1;3;5;7;9}, or

=uniqc("number text","and($$>10,mod($$,2)=1)",
"a",2,"b",5,99,MOD(ROW(INDIRECT("1:40")),20))

which returns {99;11;13;15;17;19}, or

=uniqc("number text","len(""$$"")=1",
"a","b",2,5,99,MOD(ROW(INDIRECT("1:40")),20))

which returns {"a";"b";2;5;1;3;4;6;7;8;9;0}.

Alan Beban wrote...
....
>There is no question that each search can be accomplished with the use
>of relatively simple but individually tailored built-in functions. But
>that seems beside the point of the original posting, which suggested
>that a simple function, of the nature of
>
>COUNTU(Rng, Criterion)
....

The problem is there's no end to what could be valid criterion
arguments. The ideal would be accepting COUNTIF/SUMIF-like criteria,
but that'd lead to multiple criteria issues.

There comes a point when it makes no sense to customize a function any
further. Generally, as general as possible is best. A rich set of
criterion strings is arguably going too far. Also, there are times the
best response to OPs is 'what you want to do could be done better . .
..' IMO, this is a situation in which the flexibility of subexpressions
is clearly preferable to the rigidity of elaborately specific udfs.

Harlan Grove wrote:
> ...
>Can't your ArrayUniques function handle usage like
>
> =ROWS(ArrayUniques(IF(ISTEXT(x),x)))-(COUNTIF(x,FALSE)=0)?
>

Any user who seriously wanted tha answer to the above question could
simply try it and see (the answer is yes).

> And as for boolean values, FAR BETTER just to use
>
> =(COUNTIF(x,FALSE)>0)+(COUNTIF(x,TRUE)>0)
>

There is no question that each search can be accomplished with the use
of relatively simple but individually tailored built-in functions. But
that seems beside the point of the original posting, which suggested
that a simple function, of the nature of

COUNTU(Rng, Criterion)

would be useful for counting the unique elements in a range. Though the
OP's focus was on a *built-in* function, I have been making suggestions
that could lead to a UDF of the above form that, though certainly
involving the overhead involved in a UDF compared to a built-in
function, might nevertheless provide the sought functionality. If
anyone is interested in pursuing this further, post back describing the
criteria that the function should accommodate; from what has already
been provided, it is likely trivial to reduce it to the form of

COUNTU(Rng, Criterion)

Alan Beban

"Alan Beban" <[email protected]> wrote...
....
>The following is an extended ArrayUniques function that can be used to
>count unique elements in a range. It can be used independently of the
>other functions at http:/home.pacbell.net/beban in versions later than
>xl2000 or if the number of elements in the range is 5461 or less. To
>return the count it could be called like
>
>=ROWS(ExtendedArrayUniques(Rng))
....
>The acceptable parameters for Criterion, in addition to no entry, are
>
>"ISTEXT"
>"ISNUMBER"
>"ISERROR"
>"ISLOGICAL"
>"PositiveNumbers"
>"NumbersOrText"
....

Adding special cases to general purpose functions is STUPID, but I doubt you
could understand why. Can't your ArrayUniques function handle usage like

=ROWS(ArrayUniques(IF(ISTEXT(x),x)))-(COUNTIF(x,FALSE)=0)

And as for boolean values, FAR BETTER just to use

=(COUNTIF(x,FALSE)>0)+(COUNTIF(x,TRUE)>0)

"Alan Beban" <[email protected]> wrote...
>Harlan Grove wrote:
>> . . .
>>But more to the point, if all one wants to do is *COUNT*
>>the distinct entries in a range, why would one ever want
>>to use a slow udf rather than the much faster
>>
>>=SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&""))
>>
>>?
>
>Because the range may contain error values.

Oh so clever to add this after I considered the possibility in an earlier
response. Also, to deal with the nastier problem of both zero length strings
and truly blank cells in the range as well as error values, a strictly
non-udf formula is still possible, like the following array formula.

=SUM(1/MMULT(--(IF(ISNUMBER(1/(x<>"")),x)
=TRANSPOSE(IF(ISNUMBER(1/(x<>"")),x))),ROW(x)^0))-1

Harlan Grove wrote:
> . . .
>
> Since ArrayUniques has the function definition
>
> Function ArrayUniques(InputArray, _
> Optional MatchCase As Boolean = True, _
> Optional Base_Orient As String = "1vert", _
> Optional OmitBlanks As Boolean = True)
>
> I can see how it could include or exclude blanks, but how would one
> need to call it to count distinct numbers or text in ranges that could
> also include booleans and errors?
>

The following is an extended ArrayUniques function that can be used to
count unique elements in a range. It can be used independently of the
other functions at http:/home.pacbell.net/beban in versions later than
xl2000 or if the number of elements in the range is 5461 or less. To
return the count it could be called like

=ROWS(ExtendedArrayUniques(Rng))

Called that way it will return the number of unique entries in Rng,
omitting any blanks (by default; adjusted with the 4th argument) and
invoking case matching (by default; adjusted with the 2nd argument). The
3rd argument is irrelevant to the counting function, used only to
control the orientation (horizontal or vertical, and the base (0 or 1)
of the array output by the function. The function definition is

Function ExtendedArrayUniques(InputArray, _
Optional MatchCase As Boolean = True, _
Optional Base_Orient As String = "1vert", _
Optional OmitBlanks As Boolean = True, _
Optional Criterion As String)

The acceptable parameters for Criterion, in addition to no entry, are

"ISTEXT"
"ISNUMBER"
"ISERROR"
"ISLOGICAL"
"PositiveNumbers"
"NumbersOrText"

If any other parameter is entered for Criterion, it will return a
#VALUE! error. Watch for word wrap.

Function ExtendedArrayUniques(InputArray, _
Optional MatchCase As Boolean = True, _
Optional Base_Orient As String = "1vert", _
Optional OmitBlanks As Boolean = True, _
Optional Criterion As String)
'THIS PROCEDURE REQUIRES A PROJECT REFERENCE
'TO "MICROSOFT SCRIPTING RUNTIME".
'The function returns an array of unique
'values from an array or range. By default
'it returns a 1-based vertical array; for
'other results enter "0horiz", "1horiz" or
'"0vert" as the third argument. By default,
'the function is case-sensitive; i.e., e.g.,
'"red" and "Red" are treated as two separate
'unique values; to avoid case-sensitivity,
'enter False as the second argument.

'Declare the variables
Dim arr, arr2
Dim i As Long, p As Object, q As String
Dim Elem, x As Dictionary
Dim CalledDirectFromWorksheet As Boolean
'For later use in selecting cells for worksheet output
CalledDirectFromWorksheet = False
If TypeOf Application.Caller Is Range Then
Set p = Application.Caller
q = p.Address
iRows = Range(q).Rows.count
iCols = Range(q).Columns.count
If InStr(1, p.FormulaArray, "ExtendedArrayUniques") = 2 _
Or InStr(1, p.FormulaArray, "extendedarrayuniques") = 2 _
Or InStr(1, p.FormulaArray, "EXTENDEDARRAYUNIQUES") = 2 Then
CalledDirectFromWorksheet = True
End If
End If

'Convert an input range to a VBA array
arr = InputArray

'Load the unique elements into a Dictionary Object
Set x = New Dictionary
x.CompareMode = Abs(Not MatchCase) '<--Case-sensitivity
On Error Resume Next
Select Case Criterion
Case ""
For Each Elem In arr
x.Add Item:=Elem, key:=CStr(Elem)
Next
If OmitBlanks Then x.Remove ("")
Case "ISTEXT"
For Each Elem In arr
If Application.IsText(Elem) Then x.Add Item:=Elem,
key:=CStr(Elem)
Next
If OmitBlanks Then x.Remove ("")
Case "ISERROR"
For Each Elem In arr
If Application.IsError(Elem) Then x.Add Item:=Elem,
key:=CStr(Elem)
Next
Case "ISLOGICAL"
For Each Elem In arr
If Application.IsLogical(Elem) Then x.Add Item:=Elem,
key:=CStr(Elem)
Next
Case "PositiveNumbers"
For Each Elem In arr
If Application.IsNumber(Elem) And Elem > 0 Then
If Not IsError(Elem) Then x.Add Item:=Elem,
key:=CStr(Elem)
End If
Next
Case "NumbersOrText"
For Each Elem In arr
If Application.IsNumber(Elem) Or
Application.IsText(Elem) Then
x.Add Item:=Elem, key:=CStr(Elem)
If OmitBlanks Then x.Remove ("")
End If
Next
Case Else
ExtendedArrayUniques = CVErr(xlValue)
End Select
On Error GoTo 0

'Load a 0-based horizontal array with the unique
'elements from the Dictionary Object
arr2 = x.Items

'This provides appropriate base and orientation
'of the output array
Select Case Base_Orient
Case "0horiz"
arr2 = arr2
Case "1horiz"
ReDim Preserve arr2(1 To UBound(arr2) + 1)
Case "0vert"
If x.count < 5461 Or Application.Version > 9 Then
arr2 = Application.Transpose(arr2)
Else
arr2 = ArrayTranspose(arr2)
End If
Case "1vert"
If UBound(arr2) = -1 Then
If CalledDirectFromWorksheet Then
ExtendedArrayUniques = CVErr(xlValue)
Else
ExtendedArrayUniques = [#Value!]
End If
Exit Function
End If
ReDim Preserve arr2(1 To UBound(arr2) + 1)
If x.count < 5461 Or Application.Version > 9 Then
arr2 = Application.Transpose(arr2)
Else
arr2 = ArrayTranspose(arr2)
End If
End Select
'Assure that enough cells are selected to accommodate output
If CalledDirectFromWorksheet Then
If Range(Application.Caller.Address).count < x.count Then
ExtendedArrayUniques = "Select a range of at least " & x.count
& " cells"
Exit Function
End If
End If

ExtendedArrayUniques = arr2

End Function

Alan Beban

The following is a UDF that provides for the seven cases set forth by
Harlan Grove below; watch for word wrap:.

Function COUNTU(InputRange, Criterion)
Rng = InputRange.Address
Select Case Criterion
Case "NonBlankText"
COUNTU =
Evaluate("=SUMPRODUCT(ISTEXT(Rng)*ISNUMBER(1/(Rng<>""""))/COUNTIF(Rng,Rng&""""))")
Case "PositiveNumbers"
COUNTU =
Evaluate("=SUMPRODUCT(ISNUMBER(Rng)*ISNUMBER(1/(Rng>0))/COUNTIF(Rng,Rng&""""))")
Case "NumbersOrText"
COUNTU =
Evaluate("=SUMPRODUCT((ISNUMBER(Rng)+ISTEXT(Rng))/COUNTIF(Rng,Rng&""""))")
Case Else
COUNTU = Evaluate("=SumProduct(" & Criterion & "(" & Rng &
") /" & _
"CountIf(" & Rng & ", " & Rng & "&""""))")
End Select
End Function

The parameters for Criterion are, respectively:

"ISNUMBER"
"ISTEXT"
"NonBlankText"
"ISERROR"
"ISLOGICAL"
"PositiveNumbers"
"NumbersOrText"

As mentioned in an earlier post, the results for "ISTEXT" and
"NumbersOrText" work only if the range does not include blank cells (as
contrasted with cells containing the empty string--i.e.,"")

Alan Beban

Harlan Grove wrote:
> Alan Beban wrote...
>
>>Harlan Grove wrote:
>. . .
>
> Fine. =ROWS(ArrayUniques(A1:A15)) returns the distinct count excluding
> blanks, and =ROWS(ArrayUniques(A1:A15,,,FALSE)) returns the distinct
> count including blanks.
>
> To count only distinct numbers in Rng,
>
> =SUMPRODUCT(ISNUMBER(Rng)/COUNTIF(Rng,Rng&""))
>
>
> To count only distinct text including "" in Rng,
>
> =SUMPRODUCT(ISTEXT(Rng)/COUNTIF(Rng,Rng&""))
>
>
> To count only distinct text excluding "" in Rng,
>
> =SUMPRODUCT(ISTEXT(Rng)*ISNUMBER(1/(Rng<>""))/COUNTIF(Rng,Rng&""))
>
>
> To count distinct error values in Rng,
>
> =SUMPRODUCT(ISERROR(Rng)/COUNTIF(Rng,Rng&""))
>
>
> To count distinct logical values in Rng,
>
> =SUMPRODUCT(ISLOGICAL(Rng)/COUNTIF(Rng,Rng&""))
>
>
> To count distinct positive numbers in range,
>
> =SUMPRODUCT(ISNUMBER(Rng)*ISNUMBER(1/(Rng>0))/COUNTIF(Rng,Rng&""))
>
>
> To count distinct numbers or text in Rng,
>
> =SUMPRODUCT((ISNUMBER(Rng)+ISTEXT(Rng))/COUNTIF(Rng,Rng&""))

Harlan Grove wrote:
>. . .
>
> To count only distinct text including "" in Rng,
>
> =SUMPRODUCT(ISTEXT(Rng)/COUNTIF(Rng,Rng&""))
> . . .
> To count distinct numbers or text in Rng,
>
> =SUMPRODUCT((ISNUMBER(Rng)+ISTEXT(Rng))/COUNTIF(Rng,Rng&""))
>
>
> Are we getting the point yet?
>
>. . . .

Neither of the above works if the range includes blank cells (as
contrasted with cells that contain the empty string).

Alan Beban

Harlan Grove wrote:
> . . .
> But more to the point, if all one wants to do is *COUNT*
> the distinct entries in a range, why would one ever want
> to use a slow udf rather than the much faster
>
> =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&""))
>
> ?
>
Because the range may contain error values.

Alan Beban

Alan Beban wrote...
>Harlan Grove wrote:
....
>>But more to the point, if all one wants to do is *COUNT*
>>the distinct entries in a range, why would one ever want
>>to use a slow udf rather than the much faster
>>
>>=SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&""))
>>
>> ?
>
>None of the above suggestions deals with *all* of the OP's specs, which
>Harlan Grove omitted in his post.
>
>"It should have options for counting numbers, numbers + text, excluding
>blank cells, etc."
>
>For whatever it may be worth
>
>=ArrayCount(ArrayUniques(a1:a15)) returns the number of unique entries
>excluding blanks; =ArrayCount(ArrayUniques(a1:a15,,,False) returns the
>number of unique entries including blanks.

Missing a right parenthesis on that second formula.

>=COUNTA(ArrayUniques(A1:A15)and =COUNTA(ArrayUniques(A1:A15),,,False)
>both return the number of unique entries excluding blanks.
....

Missing another right parenthesis on the first formula and looks like a
prematute right parenthesis in the second formula.

Fine. =ROWS(ArrayUniques(A1:A15)) returns the distinct count excluding
blanks, and =ROWS(ArrayUniques(A1:A15,,,FALSE)) returns the distinct
count including blanks.

To count only distinct numbers in Rng,

=SUMPRODUCT(ISNUMBER(Rng)/COUNTIF(Rng,Rng&""))


To count only distinct text including "" in Rng,

=SUMPRODUCT(ISTEXT(Rng)/COUNTIF(Rng,Rng&""))


To count only distinct text excluding "" in Rng,

=SUMPRODUCT(ISTEXT(Rng)*ISNUMBER(1/(Rng<>""))/COUNTIF(Rng,Rng&""))


To count distinct error values in Rng,

=SUMPRODUCT(ISERROR(Rng)/COUNTIF(Rng,Rng&""))


To count distinct logical values in Rng,

=SUMPRODUCT(ISLOGICAL(Rng)/COUNTIF(Rng,Rng&""))


To count distinct positive numbers in range,

=SUMPRODUCT(ISNUMBER(Rng)*ISNUMBER(1/(Rng>0))/COUNTIF(Rng,Rng&""))


To count distinct numbers or text in Rng,

=SUMPRODUCT((ISNUMBER(Rng)+ISTEXT(Rng))/COUNTIF(Rng,Rng&""))


Are we getting the point yet?


Since ArrayUniques has the function definition

Function ArrayUniques(InputArray, _
Optional MatchCase As Boolean = True, _
Optional Base_Orient As String = "1vert", _
Optional OmitBlanks As Boolean = True)

I can see how it could include or exclude blanks, but how would one
need to call it to count distinct numbers or text in ranges that could
also include booleans and errors?

Harlan Grove wrote:
> Alan Beban wrote...
>
>>Or, if the functions in the freely downloadable file at
>>http:/home.pacbell.net/beban are available to your workbook
>>
>>=ArrayCount(ArrayUniques(a1:a15))
>
> ...
>
> Someone's got to ask, why use ArrayCount rather than the
> built-in COUNTA function, as in
>
> =COUNTA(ArrayUniques(A1:A15))
>
> ?
>
> But more to the point, if all one wants to do is *COUNT*
> the distinct entries in a range, why would one ever want
> to use a slow udf rather than the much faster
>
> =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&""))
>
> ?
>
None of the above suggestions deals with *all* of the OP's specs, which
Harlan Grove omitted in his post.

"It should have options for counting
numbers, numbers + text, excluding blank cells, etc."

For whatever it may be worth

=ArrayCount(ArrayUniques(a1:a15)) returns the number of unique entries
excluding blanks; =ArrayCount(ArrayUniques(a1:a15,,,False) returns the
number of unique entries including blanks.

=COUNTA(ArrayUniques(A1:A15)and =COUNTA(ArrayUniques(A1:A15),,,False)
both return the number of unique entries excluding blanks.

Additional suggestions would be required to deal with *all* the OP's specs.

Alan Beban

Alan Beban wrote...
>Or, if the functions in the freely downloadable file at
>http:/home.pacbell.net/beban are available to your workbook
>
>=ArrayCount(ArrayUniques(a1:a15))
....

Someone's got to ask, why use ArrayCount rather than the
built-in COUNTA function, as in

=COUNTA(ArrayUniques(A1:A15))

?

But more to the point, if all one wants to do is *COUNT*
the distinct entries in a range, why would one ever want
to use a slow udf rather than the much faster

=SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&""))

?

Hi,

I also use these versions of the formula posted by Biff:

=SUMPRODUCT((ISNUMBER(A1:A15)+ISTEXT(A1:A15))/COUNTIF(A1:A15,A1:A15&""))

=SUMPRODUCT((ISNUMBER(A1:A15)+ISTEXT(A1:A15)+ISLOGICAL(A1:A15)/COUNTIF(A1:A15,A1:A15&""))

=SUMPRODUCT((ISNUMBER(A1:A15)+ISTEXT(A1:A15)+ISLOGICAL(A1:A15)+ISERROR(A1:A15))/COUNTIF(A1:A15,A1:A15&""))

which give a lot of flexibility in what to count and are "error-tolerant".
You just take out the types you don't want to be counted.

I couldn't however figure out how to combine 'error-tolerance' and counting
in the "blank" cells as there may be "" returned by formulas which are not
recognised by ISBLANK function.

I do recognize they may become a bit long, but they don't seem to be too
complex and again they are quite flexible.

Regards,
KL



"Biff" <[email protected]> wrote in message
news:[email protected]...
> Hi!
>
>> In order to count the number of unique entries in a range of cells, a
>> very
>> complicated nested set of functions (SUM, IF, FREQUENCY, MATCH, LEN,
>> etc.) is
>> required in Excel.
>
> Not really!
>
> =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&""))
>
> Biff
>
> "WayneL" <[email protected]> wrote in message
> news:[email protected]...
>> In order to count the number of unique entries in a range of cells, a
>> very
>> complicated nested set of functions (SUM, IF, FREQUENCY, MATCH, LEN,
>> etc.) is
>> required in Excel.
>>
>> I suggest adding a COUNTU worksheet function that would automatically
>> count
>> the number of unique data entries. It should have options for counting
>> numbers, numbers + text, excluding blank cells, etc.
>>
>> Thanks,
>> Wayne
>>
>> ----------------
>> This post is a suggestion for Microsoft, and Microsoft responds to the
>> suggestions with the most votes. To vote for this suggestion, click the
>> "I
>> Agree" button in the message pane. If you do not see the button, follow
>> this
>> link to open the suggestion in the Microsoft Web-based Newsreader and
>> then
>> click "I Agree" in the message pane.
>>
>> http://www.microsoft.com/office/comm...heet.functions
>
>

WayneL wrote:
> In order to count the number of unique entries in a range of cells, a very
> complicated nested set of functions (SUM, IF, FREQUENCY, MATCH, LEN, etc.) is
> required in Excel.
>
> I suggest adding a COUNTU worksheet function that would automatically count
> the number of unique data entries. It should have options for counting
> numbers, numbers + text, excluding blank cells, etc.
>
> Thanks,
> Wayne
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
> http://www.microsoft.com/office/comm...heet.functions

A good candidate is Longre's (updated) COUNTDIFF from his morefunc add-in.

Or, if the functions in the freely downloadable file at
http:/home.pacbell.net/beban are available to your workbook

=ArrayCount(ArrayUniques(a1:a15))

Alan Beban

Biff wrote:
> Hi!
>
>
>>In order to count the number of unique entries in a range of cells, a very
>>complicated nested set of functions (SUM, IF, FREQUENCY, MATCH, LEN, etc.)
>>is
>>required in Excel.
>
>
> Not really!
>
> =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&""))
>
> Biff
>
> "WayneL" <[email protected]> wrote in message
> news:[email protected]...
>
>>In order to count the number of unique entries in a range of cells, a very
>>complicated nested set of functions (SUM, IF, FREQUENCY, MATCH, LEN, etc.)
>>is
>>required in Excel.
>>
>>I suggest adding a COUNTU worksheet function that would automatically
>>count
>>the number of unique data entries. It should have options for counting
>>numbers, numbers + text, excluding blank cells, etc.
>>
>>Thanks,
>>Wayne
>>
>>----------------
>>This post is a suggestion for Microsoft, and Microsoft responds to the
>>suggestions with the most votes. To vote for this suggestion, click the "I
>>Agree" button in the message pane. If you do not see the button, follow
>>this
>>link to open the suggestion in the Microsoft Web-based Newsreader and then
>>click "I Agree" in the message pane.
>>
>>http://www.microsoft.com/office/comm...heet.functions
>
>
>

In order to count the number of unique entries in a range of cells, a very
complicated nested set of functions (SUM, IF, FREQUENCY, MATCH, LEN, etc.) is
required in Excel.

I suggest adding a COUNTU worksheet function that would automatically count
the number of unique data entries. It should have options for counting
numbers, numbers + text, excluding blank cells, etc.

Thanks,
Wayne

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions

Harlan Grove wrote:
> ...
>Can't your ArrayUniques function handle usage like
>
> =ROWS(ArrayUniques(IF(ISTEXT(x),x)))-(COUNTIF(x,FALSE)=0)?
>

Any user who seriously wanted tha answer to the above question could
simply try it and see (the answer is yes).

> And as for boolean values, FAR BETTER just to use
>
> =(COUNTIF(x,FALSE)>0)+(COUNTIF(x,TRUE)>0)
>

There is no question that each search can be accomplished with the use
of relatively simple but individually tailored built-in functions. But
that seems beside the point of the original posting, which suggested
that a simple function, of the nature of

COUNTU(Rng, Criterion)

would be useful for counting the unique elements in a range. Though the
OP's focus was on a *built-in* function, I have been making suggestions
that could lead to a UDF of the above form that, though certainly
involving the overhead involved in a UDF compared to a built-in
function, might nevertheless provide the sought functionality. If
anyone is interested in pursuing this further, post back describing the
criteria that the function should accommodate; from what has already
been provided, it is likely trivial to reduce it to the form of

COUNTU(Rng, Criterion)

Alan Beban

Alan Beban wrote...
....
>There is no question that each search can be accomplished with the use
>of relatively simple but individually tailored built-in functions. But
>that seems beside the point of the original posting, which suggested
>that a simple function, of the nature of
>
>COUNTU(Rng, Criterion)
....

The problem is there's no end to what could be valid criterion
arguments. The ideal would be accepting COUNTIF/SUMIF-like criteria,
but that'd lead to multiple criteria issues.

There comes a point when it makes no sense to customize a function any
further. Generally, as general as possible is best. A rich set of
criterion strings is arguably going too far. Also, there are times the
best response to OPs is 'what you want to do could be done better . .
..' IMO, this is a situation in which the flexibility of subexpressions
is clearly preferable to the rigidity of elaborately specific udfs.

Harlan Grove wrote...
....
>There comes a point when it makes no sense to customize a function any
>further. Generally, as general as possible is best. A rich set of
>criterion strings is arguably going too far. Also, there are times the
>best response to OPs is 'what you want to do could be done better . .
>.' IMO, this is a situation in which the flexibility of subexpressions
>is clearly preferable to the rigidity of elaborately specific udfs.

Nevertheless, I kept thinking about this. Being a bit of a closet
systems programmer, this is what I came up with.


Function uniqc( _
t As String, _
c As String, _
ParamArray a() As Variant _
) As Variant
'---------------------------------------------------------------
'requires Windows Scripting Runtime and Excel 2000 or higher or
'an Excel 97/VBA5.* implementation of VBA6's Replace function
'---------------------------------------------------------------
Const ALLTYPES As String = "Blank Number Date Text Error Logical"
Static d As Object, s As Long
Dim x As Variant, y As Variant, n As Long, cc As String

On Error GoTo CleanUp

If t = "" Or t = "*" Or t = "all" Then t = ALLTYPES

t = Replace(t, "Blank", "Empty", , , vbTextCompare)
t = Replace(t, "Number", "Double", , , vbTextCompare)
t = Replace(t, "Text", "String", , , vbTextCompare)
t = Replace(t, "Logical", "Boolean", , , vbTextCompare)

If s = 0 Then Set d = New Dictionary

s = s + 1

For Each x In a
If TypeOf x Is Range Then
For Each y In x.Areas
Call uniqc(t, c, y.Value)
Next y
ElseIf IsArray(x) Then
For Each y In x
Call uniqc(t, c, y)
Next y
ElseIf InStr(1, t, TypeName(x), vbTextCompare) > 0 _
And Not d.Exists(x) Then _
cc = IIf(c = "", "TRUE", Replace(c, "$$", x))
If Evaluate(cc) Then d.Add Key:=x, Item:=""
End If
Next x

s = s - 1

If s > 0 Then Exit Function

CleanUp:
x = d.Keys
d.RemoveAll
Set d = Nothing
s = 0

ReDim y(LBound(x) To UBound(x), 1 To 1)

For n = LBound(x) To UBound(y, 1)
y(n, 1) = x(n)
Next n

uniqc = y

Erase x, y
End Function


Sample usage:

=uniqc("number","mod($$,2)=1",MOD(ROW(INDIRECT("1:20")),10))

which returns {1;3;5;7;9}, or

=uniqc("number text","and($$>10,mod($$,2)=1)",
"a",2,"b",5,99,MOD(ROW(INDIRECT("1:40")),20))

which returns {99;11;13;15;17;19}, or

=uniqc("number text","len(""$$"")=1",
"a","b",2,5,99,MOD(ROW(INDIRECT("1:40")),20))

which returns {"a";"b";2;5;1;3;4;6;7;8;9;0}.