Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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