Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    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,
    Marty
    Attached Files Attached Files
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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 ctrl-shift-enter):

    =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

  3. #3
    Star Lounger
    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 eco-post is made of recycled electrons

  4. #4
    2 Star Lounger
    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...

    Marty
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

  5. #5
    Super Moderator WebGenii's Avatar
    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

  6. #6
    2 Star Lounger
    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

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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

  8. #8
    2 Star Lounger
    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,
    Marty
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

  9. #9
    WS Lounge VIP sdckapr's Avatar
    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

  10. #10
    2 Star Lounger
    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,
    Marty
    Attached Files Attached Files
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

  11. #11
    Star Lounger
    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))
    where E2 is the number to look for. Validate the formula above with Ctrl-Shift-Enter to create an array formula.

    EDIT: This formula doesn't take into account the multiple presence of the same number in one cell...
    This eco-post is made of recycled electrons

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Perhaps the array-formula (confirm with ctrl-shift-enter)?

    =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 10-19, 21, 31, etc then you can try this formula which assumes commas are delimiters (after changing spaces and dashes to commas). Confirm with ctrl-shift-enter:

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

  13. #13
    2 Star Lounger
    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,
    Marty
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

Posting Permissions

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