Home |
Search |
Today's Posts |
#1
|
|||
|
|||
"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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
"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
|
|||
|
|||
"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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unique Count sensitive to hidden/filtered rows | Excel Worksheet Functions | |||
Using FREQUENCY Function to Count | Excel Worksheet Functions | |||
Excel should have a quick and simple "change case" function like . | Excel Worksheet Functions | |||
Excel function help facilities | Excel Discussion (Misc queries) | |||
I cant use englisch function names in a swedich version of excel | Excel Discussion (Misc queries) |