Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Counting a Time Range with COUNTIFS or SUMIFS

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Counting a Time Range with COUNTIFS or SUMIFS

    Good Morning

    I have an issue. I've been trying to create a formula to count the number of instances a particular time range appears in a column on a spreadsheet.

    Column C contains manually entered times followed by AM or PM. One problem I've run into that may or may not be effecting the formula's I've tried is that the time formats are different in some columns.

    Example
    Data Type = General
    12:17 PM

    Example
    Data Type = Custom
    12:02:00 PM

    I've tried many methods to make them all the same format. I would prefer the one with the General data type.

    I tried converting the data type. Copying and pasting one with the format I wanted and change it in the cell, no luck.

    =SUMIFS(A:A,">=6:00AM", A:A, "<3:00PM")

    My desired results were not yielded from that formula. I've tried other formulas without the correct result either. I don't want to manually count over 350 times.

  2. #2
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,410
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Counting a Time Range with COUNTIFS or SUMIFS

    Hello,

    The main question is to determine if you are dealing with Text or dealing with Numbers ...

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,261
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Counting a Time Range with COUNTIFS or SUMIFS

    Try this

     ABCDE
    1  TIME Result
    2  05:20:00 a.m. 6
    3  06:45:00 a.m.  
    4  08:10:00 a.m.  
    5  09:35:00 a.m.  
    6  11:00:00 a.m.  
    7  12:25:00 p.m.  
    8  01:50:00 p.m.  
    9  03:15:00 p.m.  
    10  04:40:00 p.m.  
    11  06:05:00 p.m.  
    12  07:30:00 p.m.  
    13  08:55:00 p.m.  

    CellFormula
    E2=COUNTIFS(C2:C19,">=06:00",C2:C19,"<=15:00")
    Regards Dante Amor

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Counting a Time Range with COUNTIFS or SUMIFS

    The main question is to determine if you are dealing with Text or dealing with Numbers ...
    You can quickly verify with the ISNUMBER function (as dates/times are stored in Excel as numbers).

    So, if your time entry is in the cell A1, then enter this formula anywhere:
    =ISNUMBER(A1)
    If it returns TRUE, you have a valid date/time entry.
    If it returns FALSE, you actually have a text entry.

    In order to do date/time math on it, you will want it to be a valid date/time entry.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting a Time Range with COUNTIFS or SUMIFS

    Quote Originally Posted by Joe4 View Post
    You can quickly verify with the ISNUMBER function (as dates/times are stored in Excel as numbers).

    So, if your time entry is in the cell A1, then enter this formula anywhere:
    =ISNUMBER(A1)
    If it returns TRUE, you have a valid date/time entry.
    If it returns FALSE, you actually have a text entry.

    In order to do date/time math on it, you will want it to be a valid date/time entry.
    70 = True
    380 = False

  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting a Time Range with COUNTIFS or SUMIFS

    I still didn't yield the appropriate count. Perhaps it has something to do with them not being numbers? See response below 70 are numbers, 380 aren't.

  7. #7
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,410
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Counting a Time Range with COUNTIFS or SUMIFS

    Quote Originally Posted by Josh08 View Post
    I still didn't yield the appropriate count. Perhaps it has something to do with them not being numbers? See response below 70 are numbers, 380 aren't.
    Are you saying that within your column ... certains cells are Numbers and some others are Text ...???

  8. #8
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting a Time Range with COUNTIFS or SUMIFS

    Quote Originally Posted by James006 View Post
    Are you saying that within your column ... certains cells are Numbers and some others are Text ...???

    I think I'm almost there. I was able to convert the values to numbers successfully. I now have a count of 370 numeric Time values in Column B of my spreadsheet.

    I'm trying to capture Action Requests from 1-3rd shift. However with my COUNTIFS (3 statements) I'm short by 13 total. I have 357 not 370.

    1st Shift (6:00AM - 3:00PM) - =COUNTIFS('Request Tracker'!C:C,">6:00",'Request Tracker'!C:C,"<15:00")
    2nd Shift (3:00 PM - 10:00PM) - =COUNTIFS('Request Tracker'!C:C,">15:00",'Request Tracker'!C:C,"<22:00")
    3rd Shift (10:00PM - 6:00AM) - =COUNTIFS('Request Tracker'!C:C,">22:00",'Request Tracker'!C:C,"<6:00")


    1st Shift = 273
    2nd Shift = 84
    3rd Shift = 0
    Total = 357

  9. #9
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting a Time Range with COUNTIFS or SUMIFS

    What the totals should be:

    1st Shift = 273
    2nd Shift = 85
    3rd Shift = 12
    Total = 370

  10. #10
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,410
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Counting a Time Range with COUNTIFS or SUMIFS

    Without your file .. difficult to guess what is in front of your eyes ...

    Most probably for 2nd shift ... you should either >= or <=

    and your third shift should be the Total minus both 1st and 2nd Shift ...

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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