Counting frequency of occurence by hour within a date/time cell

Hi guys,

hope you can help.

Here's a sample of the data I'm working with. They relate to phone calls received (other columns on the sheet too but irrelevant for these purposes.) There are over 4,000 rows in total, spanning a 3 month period (about 50 calls a day over an 8 hour period). I need to count and show:

- The number of calls received on average across a working week, per hour between 9am and 5pm.
(eg between 9am and 10am, we receive 27 calls, between 10am and 11am - 45 calls)

- The same data, but differentiated across the days of the week (eg on specifically Mondays between 9am and 10am we receive an average of 32 calls, but on tuesdays 9am and 10am it's an average of 16 calls)

Can anyone help? I've been using Countif for most of my other work but can't seem to get my head around whether it would work for this. I've seen similar posts suggesting a table array but with so many rows of different dates, I am hoping there's another way?!


Thanks

day calldate
Friday 01/05/2015 14:11
Friday 08/05/2015 11:47
Monday 09/03/2015 14:40
Friday 22/05/2015 15:33
Tuesday 19/05/2015 16:13
Tuesday 10/03/2015 09:24
Tuesday 12/05/2015 11:34
Monday 30/03/2015 11:55
Tuesday 21/04/2015 09:49
Friday 10/04/2015 15:54
Tuesday 31/03/2015 14:23
Wednesday 01/04/2015 13:12
Thursday 05/03/2015 14:25
Monday 27/04/2015 09:51
Tuesday 07/04/2015 15:36
Monday 09/03/2015 13:16
Tuesday 24/03/2015 09:19
Thursday 09/04/2015 13:38
Tuesday 31/03/2015 13:21
Tuesday 03/03/2015 09:11

Hi
Take a look at the attached file. It uses SUMPRODUCT/MOD and COUNTIF to gain an average as the Dates and Times are together in column B.

DBY