• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

countif times in a list by the hour in excel

I have a list of times that are formatted like 03:12:00 PM. I want to categorise each time by the hour using countif so my output will show how many are in each group.

12:00 AM to 1:00 AM    20
1:00 AM to 2:00 AM   13
3:00 AM to 4:00 AM 40
etc..

I also have one other text column that will be used to filter which dates to count. This is what I have so far:

=COUNTIFS( 'Requests RAW'!$AA:$AA,"<"&Z6,'Requests RAW'!$D:$D,$H$4)-COUNTIFS( 'Requests RAW'!$AA:$AA,"<"&Z7,'Requests RAW'!$D:$D,$H$4)

Z6 is start time and z7 is end time, any ideas?
0
cookiejest
Asked:
cookiejest
  • 2
  • 2
1 Solution
 
barry houdiniCommented:
Try using a single COUNTIFS function like this

=COUNTIFS( 'Requests RAW'!$AA:$AA,">="&Z6,'Requests RAW'!$AA:$AA,"<"&Z7,'Requests RAW'!$D:$D,$H$4)

regards, barry
0
 
cookiejestAuthor Commented:
For some reason it only outputs 0s??
0
 
Rory ArchibaldCommented:
That would imply no matches. Without a sample workbook we can only guess at possible causes such as misspellings, times stored as text or times including a day portion that isn't displayed for example.
0
 
cookiejestAuthor Commented:
Please find attached an example list of times and criteria.

So for example if i want a list as described above for all times that had "STANDARD" against the name
timeexample.xlsx
0
 
Rory ArchibaldCommented:
Your times actually contain a date portion that is not displayed. I would add an additional column that just contains the times, and use that in Barry's formula (and points to him please). See attached example.

Regards,
Rory
timeexample.xlsx
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now