Forum Discussion
Solved
Formula that shows number of phone call received on certain days between certain times
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 Example
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.
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!!!!!!!!!!!!!!!!!!!!!!!!!
