Solved

countif  times in a list by the hour in excel

Posted on 2011-09-21
5
246 Views
Last Modified: 2012-05-12
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
Comment
Question by:cookiejest
  • 2
  • 2
5 Comments
 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
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
 

Author Comment

by:cookiejest
Comment Utility
For some reason it only outputs 0s??
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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
 

Author Comment

by:cookiejest
Comment Utility
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now