Count on multiple criteria and time slots

[rniculae]

Hello guys,

I am facing a problem for which I couldn't find anything on this forum so far.

I am trying to find how many agents with a certain skill are available during a 30 minute time frame based on their schedule(Table1). An agent can have multiple skills and those should be reflected in Table2.


Table1

row/col	A	B	C	D	E	F	G	H	I	J
1	Agent	IN	OUT	skill1	skill2	skill3	skill4	skill5	skill6	skill7
2	AGENT1	9:00 AM	5:30 PM	1			1		1
3	AGENT2	9:00 AM	5:30 PM			1	1	1	1	1
4	AGENT3	9:00 AM	5:30 PM		1	1		1		1
5	AGENT4	12:00 PM	8:30 PM	1		1		1
6	AGENT5	12:00 PM	8:30 PM	1			1		1
7	AGENT6	6:00 PM	2:30 AM	1	1			1		1
8	AGENT7	6:00 PM	2:30 AM	1		1		1	1
9	AGENT8	5:00 PM	1:30 AM	1	1	1	1		1	1

<tbody>
</tbody>

Table2

row/col	A	B
1	Skill1
2	Time frame	Logged
3	00:00-00:30	2
4	00:30-01:00	2
5	01:00-01:30	2
6	01:30-02:00	1

<tbody>
</tbody>

So Table2 actually is created for each skill and each 30 minute timeframe.
Considering that each agent works every day from Table1 - B2 to C2 and he can have skills from D2:J2.
So on Table2-B3 I should have a formula that counts all Agents that are available between 00:00 to 00:30 and that have on column D something that is not blank considering their time on IN B:B and OUT C:C from table1.

Thanks,
Razvan

 

[rniculae]

Hey guys,

I'd really need this. Can please someone help?


Thanks
Razvan

 

[wideboydixon]

Book1
	A	B	C	D	E	F	G	H	I	J
1	Agent	IN	OUT	skill1	skill2	skill3	skill4	skill5	skill6	skill7
2	AGENT1	09:00	17:30	1			1		1
3	AGENT2	09:00	17:30			1	1	1	1	1
4	AGENT3	09:00	17:30		1	1		1		1
5	AGENT4	12:00	20:30	1		1		1
6	AGENT5	12:00	20:30	1			1		1
7	AGENT6	18:00	02:30	1	1			1		1
8	AGENT7	18:00	02:30	1		1		1	1
9	AGENT8	17:00	01:30	1	1	1	1		1	1
Sheet1

I had to split your timeframes in the second sheet to make it easier:

Book1
	A	B	C
1	skill1	1
2	Start time	End time	Logged
3	00:00	00:30	3
4	00:30	01:00	3
5	01:00	01:13	3
6	01:30	02:00	2
Sheet2

Cell Formulas
Range	Formula
B1	=MATCH($A$1,Sheet1!$D$1:$J1,0)
C3	{=SUM(IF(Sheet1!$B$2:$B$9>Sheet1!$C$2:$C$9,IF(Sheet1!$B$2:$B$9-1<=$A3,IF(Sheet1!$C$2:$C$9>=$B3,OFFSET(Sheet1!$C$2:$C$9,0,$B$1),0)),IF(Sheet1!$B$2:$B$9<=$A3,IF(Sheet1!$C$2:$C$9>=$B3,OFFSET(Sheet1!$C$2:$C$9,0,$B$1)))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[rniculae]

Hey, thanks. It works almost perfect .

On my last time frame 11:30 PM - 12:00 AM it doesn't calculate correctly. Instead it counts all agents available regarding the time frame.

Can you help me?
Thanks
Razvan

 

[wideboydixon]

Actually, other values were wrong. Give this a try instead:

Book1
	A	B	C
1	skill1	1
2	Start time	End time	Logged
3	00:00	00:30	3
4	00:30	01:00	3
5	01:00	01:30	3
6	01:30	02:00	2
7	02:00	02:30	2
8	02:30	03:00	0
9	03:00	03:30	0
10	03:30	04:00	0
11	04:00	04:30	0
12	04:30	05:00	0
13	05:00	05:30	0
14	05:30	06:00	0
15	06:00	06:30	0
16	06:30	07:00	0
17	07:00	07:30	0
18	07:30	08:00	0
19	08:00	08:30	0
20	08:30	09:00	0
21	09:00	09:30	1
22	09:30	10:00	1
23	10:00	10:30	1
24	10:30	11:00	1
25	11:00	11:30	1
26	11:30	12:00	1
27	12:00	12:30	3
28	12:30	13:00	3
29	13:00	13:30	3
30	13:30	14:00	3
31	14:00	14:30	3
32	14:30	15:00	3
33	15:00	15:30	3
34	15:30	16:00	3
35	16:00	16:30	3
36	16:30	17:00	3
37	17:00	17:30	4
38	17:30	18:00	3
39	18:00	18:30	5
40	18:30	19:00	5
41	19:00	19:30	5
42	19:30	20:00	5
43	20:00	20:30	5
44	20:30	21:00	3
45	21:00	21:30	3
46	21:30	22:00	3
47	22:00	22:30	3
48	22:30	23:00	3
49	23:00	23:30	3
50	23:30	00:00	3
Sheet2

Cell Formulas
Range	Formula
B1	=MATCH($A$1,Sheet1!$D$1:$J1,0)
C3	{=SUM(IF(Sheet1!$B$2:$B$9Sheet1!$B$2:$B$9<=$A3, IF(Sheet1!$C$2:$C$9>$A3, OFFSET(Sheet1!$C$2:$C$9,0,$B$1))), IF(Sheet1!$C$2:$C$9>$A3, OFFSET(Sheet1!$C$2:$C$9,0,$B$1), IF(Sheet1!$B$2:$B$9<=$A3, OFFSET(Sheet1!$C$2:$C$9,0,$B$1)))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[rniculae]

Hello,

It works flawlessly! Thanks!
Have a great day!

Razvan