SOLVED

Formula that shows number of phone call received on certain days between certain times

New Contributor

Hi Wonderful Smart Excel people

This problem is doing my head in and I hope someone out there can help!!

So, I'm looking for a formula to calculate how many telephone calls were received on a Friday, Saturday, etc. (cells B2:B1081), between specific times (e.g. 9:01:00 AM (cell F3) and 11.00:00 AM (cell G3) . Call Times are in Cells C3:C1081. Total Call Summary in Cell H3 etc. Cheers... 

I've attached a picture of the spreadsheet to hopefully make it clearer. THANK YOUCalls Received Spreadsheet ExampleCalls Received Spreadsheet Example

2 Replies
best response confirmed by Neds50 (New Contributor)
Solution

@Neds50 

I'd change the times in E3 and down to 12:00:00 AM, 7:00:00 AM etc. Same for I3 and down.

And enter 1 in F11 and J11.

 

In G3:

 

=COUNTIFS($B$3:$B$1081,"Friday",$C$3:$C$1081,">="&E3,$C$3:$C$1081,"<"&F3)

 

Fill down. In K3:

 

 

=COUNTIFS($B$3:$B$1081,"Saturday",$C$3:$C$1081,">="&I3,$C$3:$C$1081,"<"&J3)

 

Fill down.

Thank you so much, Hans!! You are a legend!!!!!!!!!!!!!!!!!!!!!!!!!