Solved

countif  times in a list by the hour in excel

Posted on 2011-09-21
5
255 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
ID: 36577554
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
ID: 36578611
For some reason it only outputs 0s??
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36578870
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
ID: 36578913
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
ID: 36578954
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

821 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