A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #11  
Old June 4th, 2005, 08:53 PM
Alan Beban
external usenet poster
 
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  
Old June 5th, 2005, 04:57 AM
Alan Beban
external usenet poster
 
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  
Old June 6th, 2005, 07:45 AM
Harlan Grove
external usenet poster
 
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  
Old June 6th, 2005, 08:04 AM
Harlan Grove
external usenet poster
 
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  
Old June 7th, 2005, 12:32 AM
Alan Beban
external usenet poster
 
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  
Old June 7th, 2005, 02:59 PM
Harlan Grove
external usenet poster
 
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  
Old June 7th, 2005, 10:16 PM
Harlan Grove
external usenet poster
 
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  
Old September 11th, 2007, 07:58 PM posted to microsoft.public.excel.worksheet.functions
Loup
external usenet poster
 
Posts: 7
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  
Old October 13th, 2007, 08:32 AM posted to microsoft.public.excel.worksheet.functions
edg
external usenet poster
 
Posts: 12
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  
Old September 19th, 2008, 03:07 AM posted to microsoft.public.excel.worksheet.functions
Deb in Washington
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




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Conversion SVC Worksheet Functions 9 February 28th, 2005 03:29 PM
Return a count of unique entries? Tony Running & Setting Up Queries 2 February 3rd, 2005 05:50 PM
Pivot Table Access 2000? Air-ron General Discussion 2 October 29th, 2004 06:19 PM


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


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