Results 1 to 4 of 4
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Record Counts (2000/XP)

    I am creating a workbook that collects responses fom telephone calls using a userform in Excel. The input form works correctly with new telephone response data being fed into a hidden worksheet called Data. This information is summarised in a visible worksheet called Analysis. Each response is date stamped.

    What I would like to do ( and I am in the middle of creating it ) is a form that you can input the from date and the to date of the report to be produced showing a count of each type. I have created 3 drop down boxes, one for the day, the month and the year for both the from and to dates to use to search a specific column ( H in Data).

    What I wan't to do is do a count of each type of call made within the date parameters and have the relevant data to be produced on a seperate worksheet. Got any ideas how I can do this? I thought of pivot tables but my head began to spin, DCount but I got confused. I can work out the logic just can't put it into practice.

    Jerry
    Jerry

  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

    Re: Record Counts (2000/XP)

    How about for example: (this will give count of "TypeA" change as appropriate:
    =COUNTIF(Data!H:H,"=TypeA")

    Steve

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Record Counts (2000/XP)

    Thanks for this, however

    In column A I have the possibility of three answers, External, Internal, Transfer, In column H I have the dates. In the VBA part of it I want to input the to and from date parameters. If the value of a cell in column H is between those two dates then I would like it to count it. So I may want to count the number of external calls made between two dates for example

    Jerry
    Jerry

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Record Counts (2000/XP)

    Ok, I have cracked it, it may not be the most elegant way but I have provided a cut down of the code:

    ......
    Worksheets("data").Activate
    Set rng =Range("H1:H20")
    For Each rcell In rng

    If rcell.value >= FromData And rcell.Value<=ToData Then
    CountVal = Application.WorksheetFunction.CountIf(Range("A1:A2 0"),"Internal")

    End If

    Next rcell

    ..................

    So Thanks Steve, I knew it had CountIf in there somewhere, it was just the Range thing that gets me, but I am beginning to crack that now.

    Just as a post script the ToData and FromData are variables set as Date and are the concatenated variables from the drop down boxes on a form.

    Jerry
    Jerry

Posting Permissions

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