Count Total call duration in an hour


I want to count the total call duration in one hour. This needs to be calculated automatically for daily purposes. 

See attached excel.

Column A is date/time, Column C is Agent, Column J call duration.

The question is: What time are the Agent spent on calls in one hour intervals?

If the agent made two calls from 07:00 to 08:00, what was the call duration for that 2 calls? 



This is a Q & A forum. To qualify for an answer you should have a question. Others and I are here to help, not to have our time wasted. You might present your data in a way one can read them. Since you seem to have a lot of data you may find it expedient to attach a workbook.
Variatus (rep: 933) Jan 30, '18 at 6:28 am
Sorry for waisting your time but I need an answer to the question and I have no idea to load an excel to explain it properly.
Ladybiker650 Jan 30, '18 at 6:51 am
Add to Discussion



I made the following changes to your workbook.

  1. I inserted a named range, name "Times", to cover $C$16:$C$3476
  2. I inserted a named range, name "Users", to cover $E$16:$E$3476
  3. And a third named range, name "Durations", in $J$16:$J$3476
    For these 3 ranges you should find a formula to change row 3476 automatically when the worksheet expands or contracts. That is scope of another question.
  4. I inserted the formula [J2] =E68.
    You may like to have this necessary information in another cell, certainly get it there by another means, but that is scope for yet another question.

Now you can uses the following two formulas.

[M2] =COUNTIFS(Times,">="&$K2,Times,"<"&$L2,Users,$J$2)


[N2] =SUMIFS(Durations,Times,">="&$K2,Times,"<"&$L2,Users,$J$2)

Copy down to M12:N12 and be sure to format column N as Time.


Answer the Question

You must create an account to use the forum. Create an Account or Login