Occupancy rate calculation

[tonyandonov88]

Hello,

I'm working on a project in call center and I need some help with one of the tasks that I need to deal with.

Every day I'm receiving a report that looks like that:

RAW DATA.JPG

I need to calculate how may orders we're received per hour and how many agents were there to process them. The final report should look like that:

SHEET1.JPG

COM1 and COM2 stays for the different groups of agents that are working on the project, I need the total amount and than I need to show how many agents were online of every group for every 1h slot.
I've managed to calculate the number of orders per hour with a formula that looks like that - =COUNTIFS('range">=" & A2;'range"<" & A3) , and this applies for every timeslot.

I really need some help with counting the number of agents per hour. In this case I think that I need a formula that counts the unique number of text entries per hour. And than another one that counts the unique text entries per hour that starts with COM1 and COM2.
I hope that I've manged to explain the issue simple enough, if you need something just ask. Thank you in advance!

Cheers

[JohnTopley]

Please post a sample file to give respondents data to work with and include manual sample results.

To upload file, click "Go Advanced" then "Manage attachments"

[tonyandonov88]

Sure, I think it's attached now

[JohnTopley]

in D2

=SUMPRODUCT(('RAW data'!$H$2:$H$14024>=A2)*('RAW data'!$H$2:$H$14024<A3)*(LEFT('RAW data'!$D$2:$D$14024,4)="COM1"))

in E2

=SUMPRODUCT(('RAW data'!$H$2:$H$14024>=A2)*('RAW data'!$H$2:$H$14024<A3)*(LEFT('RAW data'!$D$2:$D$14024,4)="COM2"))

in C2

=D2+E2

[tonyandonov88]

Call me stupid, but I think I'm doing it wrong :D
Doesn't work ...

[JohnTopley]

See attached.

I also changed times in A to be formatted as hh:mm:ss

[tonyandonov88]

Yes, it works! Problem is that I try to place it in the real file and than change the criteria from COM1 to 60k and from COM2 to neo for example, than it doesn't calculate ... why is that? I have to admit that you've been very helpful, thank you for that

[JohnTopley]

Post a small sample file with your REAL data: is COM1.AGENTx replaced by 60K.AGENTx ???

This is a common problem of NOT supplying real data!

[tonyandonov88]

There is personal information on the original file, that's why I need to edit some of the cells. I've attached a sample file that is very similar to the original. Apologize, but I can't upload the original file.

[JohnTopley]

See the attached.

I changed heading in D & E to be same as (number of) characters before full stop in Agent e,g. neo and 60k

new formula

=SUMPRODUCT(('RAW data'!$H$2:$H$14024>=A2)*('RAW data'!$H$2:$H$14024<A3)*(LEFT('RAW data'!$D$2:$D$14024,LEN(D$1))=D$1))

now matches the heading in D and E against the Agent column

[tonyandonov88]

John, you are a great man! It works perfectly, thank you so much! I'll definitely take a deep look at the formulas so I can apply this knowledge in future. Thank you once again John and if you need something around team leadership and people coaching just give me shout

[JohnTopley]

You are welcome.

Could you mark thread as solved ("Thread Tools" at top of first post).