Results 1 to 13 of 13
Thread: Frequent Numbers

20100116, 14:20 #1
 Join Date
 Jan 2004
 Location
 Brighton, Michigan
 Posts
 184
 Thanks
 10
 Thanked 0 Times in 0 Posts
Hi To All,
I am trying to count or tally how often a number or numbers appear in an excel chart. In some cases the cells contain text as well and others just numbers usually separated by a comma or a dash. I've attached a sample of my excel chart. The data as shown will always appear in a single excel column. I can manage the counting of numbers, but when text is also present I am unable to solve it.
Thank you,
MartyRegards,
Marty
"Aerodynamics Is For Those Who Cannot Build Engines"  Enzo Ferrari

20100116, 15:44 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Not sure exactly what you are after, but to count (for example) the number of 1s in the range A1:A6 you can use the array formula (confirm with ctrlshiftenter):
=SUM(LEN($A1:$A6)LEN(SUBSTITUTE($A1:$A6,1,"")))
For the 2s
=SUM(LEN($A1:$A6)LEN(SUBSTITUTE($A1:$A6,2,"")))
Etc all the way to 0
Steve

20100117, 03:37 #3
 Join Date
 Dec 2009
 Location
 Mexico City, D.F., Mexico
 Posts
 81
 Thanks
 0
 Thanked 0 Times in 0 Posts
It would definitively help if you could tell us what is the expected result in your example.
This ecopost is made of recycled electrons

20100117, 19:56 #4
 Join Date
 Jan 2004
 Location
 Brighton, Michigan
 Posts
 184
 Thanks
 10
 Thanked 0 Times in 0 Posts
Hi To All,
Sorry for not being more explicit..let me try to explain. If I have a column in excel a range say A1:A220 with some cells containing a series of numbers separated by commas, and in other rows or cells I have some text along with numbers, and I wish to tally how many times within this cell range that for example the number 8 appears of the number 256 appears. The results of this search or query could be placed in one or several cells on the same worksheet.
hopefully this helps...
Thanks again...
MartyRegards,
Marty
"Aerodynamics Is For Those Who Cannot Build Engines"  Enzo Ferrari

20100118, 11:44 #5
 Join Date
 Jan 2001
 Location
 Redcliff, Alberta, Canada
 Posts
 4,066
 Thanks
 2
 Thanked 5 Times in 5 Posts
What if the number 8 is part of a bigger number, like 18?
Should it be counted then?[b]Catharine Richardson (WebGenii)
WebGenii Home Page
Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

20100118, 15:42 #6
 Join Date
 Jan 2001
 Location
 Chicago, Illinois, USA
 Posts
 187
 Thanks
 6
 Thanked 0 Times in 0 Posts
parse your list using TEXT TO COLUMNS (using a comma delimeter), then use the FREQUENCY function to perform the count

20100118, 18:17 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Perhaps if you provided a few sample columns with data and the exact results that you want for each one we may be able to provide a formula or user defined function...
Steve

20100122, 11:10 #8
 Join Date
 Jan 2004
 Location
 Brighton, Michigan
 Posts
 184
 Thanks
 10
 Thanked 0 Times in 0 Posts
Hi to All,
A number will be counted if it is separated by a comma , or a hyphen . All the numbers pertaining to or contained in the date cells can be ignored, such as cells A1, A4, A7 etc.
Thank you for your help.
Regards,
MartyRegards,
Marty
"Aerodynamics Is For Those Who Cannot Build Engines"  Enzo Ferrari

20100122, 11:51 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
I am still not clear on your input and what you want the output to look like. It would be very useful to attach a sample workbook with what you have and the results you want based on that example. If the input can be dramatically different, you may want to include several different test inputs and what the outputs would be to ensure that the solution meets all the contingencies...
Steve

20100125, 11:09 #10
 Join Date
 Jan 2004
 Location
 Brighton, Michigan
 Posts
 184
 Thanks
 10
 Thanked 0 Times in 0 Posts
[attachment=87918:wopr ex2.xls]I do not think I was totally clear either. Attached is a clearer representation of what I am trying to do.....count the frequency of numbers in a specified cell range.
Hopefully this helps out.
Thank You for for patience and assistance....
Regards,
MartyRegards,
Marty
"Aerodynamics Is For Those Who Cannot Build Engines"  Enzo Ferrari

20100125, 11:46 #11
 Join Date
 Dec 2009
 Location
 Mexico City, D.F., Mexico
 Posts
 81
 Thanks
 0
 Thanked 0 Times in 0 Posts
In your example you wrote: "that appear only in the following cells in this sequence: A2,A3, A5, A6, A8,A9,A11,A12,A14,A15….A150". What is the logic to not consider the cells A4, A7, A10, A13? Is it because they start with the text "Shift"? In such case, maybe we could consider having a second column like =IF(Left(A4,5)="Shift","",A4), then you can apply the future function on this new column. As it also seems that all the dates are in these cells starting with "Shift", we eliminate the issue of defining whether a number is part of a date or not. Finally, if a number is always surrounded by an hypen, a blank or a comma, then ""&SUBSTITUTE(SUBSTITUTE(A4,",","")," ","")&"" will replace all the separators by an hyphen so each now is between two hyphens. So now, with the use of the FIND function which returns an error if a text is not found within another text, and using a function array, the automagic formula is:
Code:=SUM(IF(ISERR(FIND(""&E2&"",""&SUBSTITUTE(SUBSTITUTE(IF(LEFT($A$1:$A$150,5)="Shift","",$A$1:$A$150),",","")," ","")&"")),0,1))
EDIT: This formula doesn't take into account the multiple presence of the same number in one cell...This ecopost is made of recycled electrons

20100125, 13:31 #12
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Perhaps the arrayformula (confirm with ctrlshiftenter)?
=SUM(IF(LEFT($A$1:$A$150,5)<>"Shift",(LEN($A$1:$A$ 150)LEN(SUBSTITUTE($A$1:$A$150,E2,"")))))/LEN(E2)
Steve
PS the above will give the total of the numbers which may not be what you want (if it has a 1 and 11, it will find 3 1s for example)
If you want to distinguish the individual value 1 and the digits 1 in numbers like 1019, 21, 31, etc then you can try this formula which assumes commas are delimiters (after changing spaces and dashes to commas). Confirm with ctrlshiftenter:
=SUM(IF(LEFT($A$1:$A$150,5)<>"Shift",(LEN(","&$A$1 :$A$150&",")LEN(SUBSTITUTE((","&SUBSTITUTE(SUBSTITUTE($A$1:$A$ 150,"",",")," ",",")&","),","&E17&",","")))))/LEN(","&E17&",")

20100202, 13:31 #13
 Join Date
 Jan 2004
 Location
 Brighton, Michigan
 Posts
 184
 Thanks
 10
 Thanked 0 Times in 0 Posts
Thank you Stephane and sdckapr,
this may take me a bit to digest, between classes. Yes cells such as 1, 4, 7, 10 can be ignored as they refer to shift...they are n/a for this purpose. Let me try this input out.....thanks for your input and patience with a formual "green horn"
Regards,
MartyRegards,
Marty
"Aerodynamics Is For Those Who Cannot Build Engines"  Enzo Ferrari