Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jun 2009
    Location
    Brisbane, Australia
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy

    Hi,

    I have a large spreadsheet that basically contains information about phone calls. Multiple calls made to multiple people on the same dates. In the real data, I have details of approx 6000 calls made over a 4 week period (incoming and outgoing), to/from a group of 74 unique people. What I would like the formula to do is calculate how many times is each of the people is called on each date. I know the end of the formula dealing with the date is the problem, I'm just unsure how to solve it.

    I am attaching a dummy workbook to give an idea of what I am trying to do (the real one has much more data, many more columns etc, but this should suffice for a sample). I have played around with a SUMPRODUCT formula (see example), but the date component is getting the better of me at the moment. Once I have set the workbook up, it will be used by a team of people who have between none and very minor knowledge of excel, so I am trying to avoid pivot tables if possible.

    Thanks in anticipatioin

    Wyldchild
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='wyldchild' post='786059' date='24-Jul-2009 16:08']Hi,

    I have a large spreadsheet that basically contains information about phone calls. Multiple calls made to multiple people on the same dates. In the real data, I have details of approx 6000 calls made over a 4 week period (incoming and outgoing), to/from a group of 74 unique people. What I would like the formula to do is calculate how many times is each of the people is called on each date. I know the end of the formula dealing with the date is the problem, I'm just unsure how to solve it.

    I am attaching a dummy workbook to give an idea of what I am trying to do (the real one has much more data, many more columns etc, but this should suffice for a sample). I have played around with a SUMPRODUCT formula (see example), but the date component is getting the better of me at the moment. Once I have set the workbook up, it will be used by a team of people who have between none and very minor knowledge of excel, so I am trying to avoid pivot tables if possible.

    Thanks in anticipatioin

    Wyldchild[/quote]
    Are you looking for something like this? I dont know how to calculate frequency of Incoming/Outgoing calls as no such identification is there in master sheet.
    Attached Files Attached Files
    Regards
    Prasad

  3. #3
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='prasad' post='786060' date='24-Jul-2009 16:18']Are you looking for something like this? I dont know how to calculate frequency of Incoming/Outgoing calls as no such identification is there in master sheet.[/quote]
    Nothing new. Just attached updated sheet.
    Attached Files Attached Files
    Regards
    Prasad

  4. #4
    New Lounger
    Join Date
    Jun 2009
    Location
    Brisbane, Australia
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Prasad,

    Perfect!! Exactly what I wanted it to do, and I can adapt it for the incoming/outgoing calls when I am at work and have the real sheet to work with. I was trying to find a complex solution using the dates from the master sheet rather than looking at the much better solution of using the date in the summary sheet. Very simple, very elegant.

    Thankyou

    Wyldchild

  5. #5
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='wyldchild' post='786062' date='24-Jul-2009 16:29']Hi Prasad,

    Perfect!! Exactly what I wanted it to do, and I can adapt it for the incoming/outgoing calls when I am at work and have the real sheet to work with. I was trying to find a complex solution using the dates from the master sheet rather than looking at the much better solution of using the date in the summary sheet. Very simple, very elegant.

    Thankyou

    Wyldchild[/quote]
    As I quoted :
    "Learn to share, share to learn."
    Regards
    Prasad

Posting Permissions

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