1. ## 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

2. ## 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. ## 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

4. ## 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

#### Posting Permissions

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