Solved

countif  times in a list by the hour in excel

Posted on 2011-09-21
5
266 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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!
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

726 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