PDA

View Full Version : Solved: Formula for count by day and hour



willhh3
04-14-2011, 03:46 PM
Hello,

I need some help figuring this one out. Essentially I need a formula to get a count of how many calls are received on a given day, during a given hour.
For example: 2 Calls were received on 1/1/2011 during the 3am hour.
I'm guessing I'd put the formula in each cell (i.e., i2,j2,k3, etc.). Please ref the attached example of to see what I'm trying to do. I am open to a better format, if you can suggest any.

Thanks!

Ken Puls
04-14-2011, 10:20 PM
I'd probably just go with a COUNTIFS formula. (I'm assuming that you're using Excel 2007 or higher.)

I'm also assuming that you want to have table output like what you've shown? (With the zeros for the times with no calls?) If you're not worried about that portion, and you just want a count by hour, you could try a pivottable on your data instead.

FYI, your dates were 2011 in the first table and 2010 in the second. I made them similar to test. I also set the first time column you had to a value of 0, which equates to 12:00am (Jan 1, 1900). You had a day in there, but your following columns were all based of Jan 1, 1900 times.

I've added a modified copy of the workbook for you.

willhh3
04-15-2011, 06:21 AM
Ken,
Thanks so much for the quick response and catching the in accuracies in my example. I was rushed when throwing it together and your assumptions were correct. I should have time today to play with your example and see if I can get to work with my actual data. Really appreaciate your help.

Willhh3