Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
WayneL
 
Posts: n/a
Default "COUNTU" function in Excel to count unique entries in a range

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...et.f unctions
  #2   Report Post  
Biff
 
Posts: n/a
Default

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" wrote in message
...
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...et.f unctions



  #3   Report Post  
Alan Beban
 
Posts: n/a
Default

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" wrote in message
...

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...et.f unctions




  #4   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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...et.f unctions


A good candidate is Longre's (updated) COUNTDIFF from his morefunc add-in.
  #5   Report Post  
KL
 
Posts: n/a
Default

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)+ISLOG ICAL(A1:A15)/COUNTIF(A1:A15,A1:A15&""))

=SUMPRODUCT((ISNUMBER(A1:A15)+ISTEXT(A1:A15)+ISLOG ICAL(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" wrote in message
...
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" wrote in message
...
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...et.f unctions







  #6   Report Post  
Harlan Grove
 
Posts: n/a
Default

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

?

  #7   Report Post  
Alan Beban
 
Posts: n/a
Default

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
  #8   Report Post  
Harlan Grove
 
Posts: n/a
Default

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/(Rng0))/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?

  #9   Report Post  
Alan Beban
 
Posts: n/a
Default

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
  #10   Report Post  
Alan Beban
 
Posts: n/a
Default

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


  #11   Report Post  
Alan Beban
 
Posts: n/a
Default

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/(Rng0))/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/(Rng0))/COUNTIF(Rng,Rng&""))


To count distinct numbers or text in Rng,

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

  #12   Report Post  
Alan Beban
 
Posts: n/a
Default

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
  #13   Report Post  
Harlan Grove
 
Posts: n/a
Default

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

?


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


  #14   Report Post  
Harlan Grove
 
Posts: n/a
Default

"Alan Beban" 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)


  #15   Report Post  
Alan Beban
 
Posts: n/a
Default

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


  #16   Report Post  
Harlan Grove
 
Posts: n/a
Default

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.

  #17   Report Post  
Harlan Grove
 
Posts: n/a
Default

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

  #18   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default "COUNTU" function in Excel to count unique entries in a range

Well I hate to add to this mess, but just reading these responses is
definately an argument for the original post. This should not be this
difficult or complicated. Most users of excell are not programers nor have
the time to fiddle with long formulas to get an answer that should be easy to
get.

"Aladin Akyurek" wrote:

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...et.f unctions


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

  #19   Report Post  
Posted to microsoft.public.excel.worksheet.functions
edg edg is offline
external usenet poster
 
Posts: 5
Default "COUNTU" function in Excel to count unique entries in a range

All you really need is an extremely simple function countif a=1 (whether
numeric or text and b=2 (again numeric or text) etc, etc, etc. Why do Excel
formulas and functions have to be so complicated. I programmed in Basic for
many years and still have difficulty figuring out how to enter Excel formulas.
--
edg


"LouP" wrote:

Well I hate to add to this mess, but just reading these responses is
definately an argument for the original post. This should not be this
difficult or complicated. Most users of excell are not programers nor have
the time to fiddle with long formulas to get an answer that should be easy to
get.

"Aladin Akyurek" wrote:

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...et.f unctions


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

  #20   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default "COUNTU" function in Excel to count unique entries in a range

AWESOME, thanks so much!! My work thanks you too b/c i have hundreds of
cells of data and need to count how many unique accts i'm assigning the
person to complete BUT have to leave all the duplicates next to each other.

"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" wrote in message
...
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...et.f unctions






  #21   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default "COUNTU" function in Excel to count unique entries in a range

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Deb in Washington" <Deb in wrote in
message ...
AWESOME, thanks so much!! My work thanks you too b/c i have hundreds of
cells of data and need to count how many unique accts i'm assigning the
person to complete BUT have to leave all the duplicates next to each
other.

"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" wrote in message
...
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...et.f unctions





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Unique Count sensitive to hidden/filtered rows Ian Excel Worksheet Functions 7 May 12th 05 08:58 PM
Using FREQUENCY Function to Count Sergio Excel Worksheet Functions 1 April 11th 05 05:16 PM
Excel should have a quick and simple "change case" function like . NinaSvendsen Excel Worksheet Functions 1 January 28th 05 04:15 PM
Excel function help facilities RPS Excel Discussion (Misc queries) 1 December 8th 04 03:36 AM
I cant use englisch function names in a swedich version of excel PE Excel Discussion (Misc queries) 2 December 7th 04 02:00 AM


All times are GMT +1. The time now is 01:26 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"