+ Reply to Thread
Results 1 to 7 of 7

Excel 2007 : How do I find the number of times a time appears in a report ?

  1. #1
    Registered User
    Join Date
    02-26-2010
    Location
    canada
    MS-Off Ver
    Excel 2003
    Posts
    6

    How do I find the number of times a time appears in a report ?

    I have been trying to create a formula that would let me calculate the number of time a particular time shows up in a report. Letís say I wanted to count how many times entries for 23:00:00 hours apeairs in the below example on Mondays. I can count 8 but is there a way to make excel count it. I have been told it is impossible and just donít believe it.
    Date 4-Jan 5-Jan 6-Jan 7-Jan 11-Jan
    Day Mon Tue Wed Thu Mon
    23:21:34 23:20:07 23:54:01 23:31:44 23:58:17
    22:54:20 23:16:25 23:43:36 23:21:08 23:47:16
    22:20:52 22:50:11 23:17:26 23:18:40 23:45:58
    21:39:22 22:47:56 22:45:00 23:16:24 23:36:41
    21:37:15 22:30:45 22:43:04 23:10:05 23:24:03
    21:36:18 22:25:20 22:13:33 22:49:48 23:17:03
    21:32:54 21:49:24 22:04:11 22:39:40 23:10:41
    20:59:06 21:42:46 21:47:20 22:31:16 22:46:39
    20:36:56 21:18:07 21:45:40 22:23:41 22:15:19
    20:29:59 20:07:09 21:29:35 22:22:14 22:03:01

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: How do I find the number of times a time appears in a report ?

    Here you can try this:

    =SUMPRODUCT(--($B$3:$F$3&HOUR($B$4:$F$13)="Mon"&23))

    (of course, you can make references instead of "Mon"&23 and put for example drop down menu in G1&H1 with Mon, tue, Wed.... and 1,2,3,4...
    Attached Files Attached Files
    Last edited by zbor; 02-26-2010 at 06:11 PM.

  3. #3
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099

    Re: How do I find the number of times a time appears in a report ?

    Hi,

    If you had Mon,Tue,Wed,Thur,Mon in H2:L2, then this in H3

    =COUNTIF(A$3:A$12,">=23:00:00") drag over to L3 this will give the answer for each day
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: How do I find the number of times a time appears in a report ?

    That won't work if he for example wants for 21:00 where it will count all up to 24:00
    Last edited by zbor; 02-26-2010 at 05:57 PM.

  5. #5
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099

    Re: How do I find the number of times a time appears in a report ?

    Yeah missed read that one, thought he was looking for anything over 23:00:00

  6. #6
    Registered User
    Join Date
    02-26-2010
    Location
    canada
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: How do I find the number of times a time appears in a report ?

    =COUNTIF(A$3:A$12,">=23:00:00") seems to total all the calls before 23:00 I think i needs to add a < some where ?

  7. #7
    Registered User
    Join Date
    02-26-2010
    Location
    canada
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: How do I find the number of times a time appears in a report ?

    An thank you every one for your help!!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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