+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 20

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

  1. #1
    WayneL
    Guest

    "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...heet.functions

  2. #2
    Biff
    Guest

    Re: "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" <WayneL@discussions.microsoft.com> wrote in message
    news:735EE93F-CCF4-41BC-9D5B-553A4CC72043@microsoft.com...
    > 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




  3. #3
    Alan Beban
    Guest

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

    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" <WayneL@discussions.microsoft.com> wrote in message
    > news:735EE93F-CCF4-41BC-9D5B-553A4CC72043@microsoft.com...
    >
    >>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

    >
    >
    >


  4. #4
    Aladin Akyurek
    Guest

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

    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.

  5. #5
    KL
    Guest

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

    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" <biffinpitt@comcast.net> wrote in message
    news:eBUmRowZFHA.1404@TK2MSFTNGP09.phx.gbl...
    > 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" <WayneL@discussions.microsoft.com> wrote in message
    > news:735EE93F-CCF4-41BC-9D5B-553A4CC72043@microsoft.com...
    >> 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

    >
    >




  6. #6
    Harlan Grove
    Guest

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

    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. #7
    Alan Beban
    Guest

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

    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. #8
    Harlan Grove
    Guest

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

    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?


  9. #9
    Alan Beban
    Guest

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

    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. #10
    Alan Beban
    Guest

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

    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. #11
    Alan Beban
    Guest

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

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


  12. #12
    Alan Beban
    Guest

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

    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. #13
    Harlan Grove
    Guest

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

    "Alan Beban" <unavailable@no.com> 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. #14
    Harlan Grove
    Guest

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

    "Alan Beban" <unavailable@no.com> 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. #15
    Alan Beban
    Guest

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

    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

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1