Count number of occurances of numbers
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Count number of occurances of numbers

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Wayne Joyner - Omaha, NE
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I would like to count the number of times series of numbers appears in a column. For example: 123 is listed 2 times and 345 is listed 4 times. I would like to be able to sum the 2 (events)and 4 (events) for a value of 6 and place that value in a cell for additional use. Anyone know of a formula to do this? I'm trying to see how many times a ticket is responded to more than once during the reporting period.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    you could countif each criteria and add them together

    say b1 contains "123" and b2 contains "345"

    and your ticket data is in range a1 to a20 :

    =COUNTIF(A1:A20,B1)+COUNTIF(A1:A20,B2)

    (I'd put your two criteria in their own cells otherwise you'd have to manually adjust your formula each time you want to change what you're looking for)





    _________________
    Hope this helps,
    Chris


    [ This Message was edited by: Chris Davison on 2002-02-18 12:57 ]

    [ This Message was edited by: Chris Davison on 2002-02-18 12:57 ]

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    Wayne Joyner - Omaha, NE
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Good start I believe. However; I will not know what the numbers are in advance. The numbers that may be common will be random ticket numbers. For example : My On-Call person takes a call at 8:00 AM on ticket 55624, then later on the next day, the ticket is addressed again at 4:17 PM. (Probably hadn't been resolved yet). The ticket has now been reviewed 2 times over the course of 2 days. I want to be able to see how many times ticket 55624 has been addressed before it is finally closed. This could go on for 15 - 100 diffrent tickets during any given period. The ticket numbers would be random in that the customer calls are assigned a number when received, but may be worked by several diffrent people before finally being closed. Each person making an entry in the log for their efforts.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-18 13:07, wjoyner wrote:
    Good start I believe. However; I will not know what the numbers are in advance. The numbers that may be common will be random ticket numbers. For example : My On-Call person takes a call at 8:00 AM on ticket 55624, then later on the next day, the ticket is addressed again at 4:17 PM. (Probably hadn't been resolved yet). The ticket has now been reviewed 2 times over the course of 2 days. I want to be able to see how many times ticket 55624 has been addressed before it is finally closed. This could go on for 15 - 100 diffrent tickets during any given period. The ticket numbers would be random in that the customer calls are assigned a number when received, but may be worked by several diffrent people before finally being closed. Each person making an entry in the log for their efforts.
    I'd recommend that you create a PivotTable that lists 'ticket number' in the ROW area and Count of 'ticket number' in the DATA area. That leaves one question: How do you signify that the ticket is closed? Do you "flag" it in a separate column?

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    Wayne Joyner - Omaha, NE
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes a seperate column is used to flag whether the ticket is closed. The columns include date, number, small issue field, persons name (working ticket (making entry)), and finally status. Status using one of three values. 'O' Open, 'X' Closed or '-' for pending.

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-18 13:41, wjoyner wrote:
    Yes a seperate column is used to flag whether the ticket is closed. The columns include date, number, small issue field, persons name (working ticket (making entry)), and finally status. Status using one of three values. 'O' Open, 'X' Closed or '-' for pending.
    Then you could include 'Status' in the PivotTable's PAGE area and set it's value to "O".

  7. #7
    New Member
    Join Date
    Feb 2002
    Location
    CD Victoria, Mexico
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi Mark, this is an add-In. I ahve a similar problem to the above, I used your formula, or close to it, modified to fit in the data area. I can not get the durn thing to feed out.
    Formula:
    =COUNTIF(F1:K966,A2)
    I continually get the same output, 0 which is null.
    I want to count how many times the number in A2 is matching the numbers in the data.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com