Count on multiple criteria and time slots

rniculae

New Member
Joined
Apr 9, 2013
Messages
41
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

[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]row/col[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Agent[/TD]
[TD]IN[/TD]
[TD]OUT[/TD]
[TD]skill1[/TD]
[TD]skill2[/TD]
[TD]skill3[/TD]
[TD]skill4[/TD]
[TD]skill5[/TD]
[TD]skill6[/TD]
[TD]skill7[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AGENT1[/TD]
[TD]9:00 AM[/TD]
[TD]5:30 PM[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]AGENT2[/TD]
[TD]9:00 AM[/TD]
[TD]5:30 PM[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]AGENT3[/TD]
[TD]9:00 AM[/TD]
[TD]5:30 PM[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]AGENT4[/TD]
[TD]12:00 PM[/TD]
[TD]8:30 PM[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]AGENT5[/TD]
[TD]12:00 PM[/TD]
[TD]8:30 PM[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]AGENT6[/TD]
[TD]6:00 PM[/TD]
[TD]2:30 AM[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]AGENT7[/TD]
[TD]6:00 PM[/TD]
[TD]2:30 AM[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]AGENT8[/TD]
[TD]5:00 PM[/TD]
[TD]1:30 AM[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


Table2

[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]row/col[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Skill1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Time frame[/TD]
[TD]Logged[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]00:00-00:30[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]00:30-01:00[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]01:00-01:30[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]01:30-02:00[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Book1
ABCDEFGHIJ
1AgentINOUTskill1skill2skill3skill4skill5skill6skill7
2AGENT109:0017:30111
3AGENT209:0017:3011111
4AGENT309:0017:301111
5AGENT412:0020:30111
6AGENT512:0020:30111
7AGENT618:0002:301111
8AGENT718:0002:301111
9AGENT817:0001:30111111
Sheet1


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


Book1
ABC
1skill11
2Start timeEnd timeLogged
300:0000:303
400:3001:003
501:0001:133
601:3002:002
Sheet2
Cell Formulas
RangeFormula
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
 
Upvote 0
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
 
Upvote 0
Actually, other values were wrong. Give this a try instead:


Book1
ABC
1skill11
2Start timeEnd timeLogged
300:0000:303
400:3001:003
501:0001:303
601:3002:002
702:0002:302
802:3003:000
903:0003:300
1003:3004:000
1104:0004:300
1204:3005:000
1305:0005:300
1405:3006:000
1506:0006:300
1606:3007:000
1707:0007:300
1807:3008:000
1908:0008:300
2008:3009:000
2109:0009:301
2209:3010:001
2310:0010:301
2410:3011:001
2511:0011:301
2611:3012:001
2712:0012:303
2812:3013:003
2913:0013:303
3013:3014:003
3114:0014:303
3214:3015:003
3315:0015:303
3415:3016:003
3516:0016:303
3616:3017:003
3717:0017:304
3817:3018:003
3918:0018:305
4018:3019:005
4119:0019:305
4219:3020:005
4320:0020:305
4420:3021:003
4521:0021:303
4621:3022:003
4722:0022:303
4822:3023:003
4923:0023:303
5023:3000:003
Sheet2
Cell Formulas
RangeFormula
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
 
Upvote 0

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top