+ Reply to Thread
Results 1 to 3 of 3

Sum number of calls within a specific time frame

  1. #1
    Registered User
    Join Date
    02-01-2013
    Location
    West Hartford, CT
    MS-Off Ver
    Excel 2013
    Posts
    8

    Sum number of calls within a specific time frame

    I have created a pivot table which properly display the time of day and the number of calls at that time. (There happens to be only one call per minute but in theory there can be multiple calls made in the same minute). I have tried to sum the calls by hour using countif and a variety of other things. Any help would be appreciated! Below is an image of what I am trying to calculate and the sample excel sheet is also attached.
    2013-12-10_1650.png
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Sum number of calls within a specific time frame

    Hi and welcome to the forum

    The main reason you cannot get the count to work is because the times in your summary table are just that - times. But the times in your data table are dates AND times...
    7/8/2013 10:35:18 AM

    To get past this, I used a helper column D (you can put it where ever you want, and then used this, copied down...
    =MOD(A3,1)

    Then to get the summaries, use this in the summary table, copied down...
    =COUNTIFS($C$3:$C$1056,">="&E3,$C$3:$C$1056,"<="&F3)

    I did notice that the totals did not match, and found out that you have some times that have seconds inthem that the above does not catch. Sor change the "end" time to hh:mm:59 - 12:59:59 AM etc
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Sum number of calls within a specific time frame

    hi t83357. other alternatives:
    in G3
    =SUMPRODUCT((FLOOR(MOD($A$3:$A$1056,1),"0:01")>=E3)*(FLOOR(MOD($A$3:$A$1056,1),"0:01")<=F3))

    or do a Pivot (which you mentioned you did?). just right-click the Time of the day & Group. Choose Hour & take out Month.
    Attached Files Attached Files

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Number of total calls in desired time frames
    By skate1991 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-31-2013, 11:37 AM
  2. [SOLVED] Assigning a category to a specific time frame
    By Woofaloo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-18-2013, 07:15 PM
  3. Excel 2007 : Adds values for a specific time frame
    By Raist23 in forum Excel General
    Replies: 3
    Last Post: 03-15-2012, 05:43 AM
  4. formula to show average over specific time frame over multiple sheets
    By bademployee in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-18-2010, 08:22 PM
  5. Number of tasks completed in a specific time frame
    By Robert in forum Excel General
    Replies: 2
    Last Post: 02-03-2006, 09:30 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1