Countif Formula

[wael_afifi]

Hi All,

Appreciate your support with a countif formula to count the number of agents scheduled for breaks in every 30min interval as attached

[TMS]

Maybe something like:

Please Login or Register  to view this content.

[wael_afifi]

Thank you TMS for your reply.

Please consider that we would like to count the number of agents starting for 6:00 AM who are scheduled for breaks

Attachment 679364

[TMS]

I don't know where you indicated that as a requirement.

Did the formula provided answer your initial question?

You attachment is invalid.

[wael_afifi]

Considering that the aim from the report is to count the number of agents scheduled for breaks in every interval starting from 6:00 AM
the previous formula was not serving the requested data.

Apologies for the corrupted attached.

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook.

[wael_afifi]

Thank you AliGW

Please find the attached report

[ORoos]

Hi wael_afifi,
I have added the formula provided by TMS into your sample file into cell B11 and pulled through to CH11. The formula works fine.

There is a problem with your 'break interval' values from cell AM10 onwards. AL10 = 12:00:00 AM AM10 = 1/01/1900 12:30:00 AM ... You must remove the date from the time.

Why do you have 'break intervals' covering more than 24 hours. The first agent is starting at 5 AM, the last at 6 PM finishing at 3 AM. No one is working between 3 AM and 5 AM. Your 'break interval' can stop after AQ10.
Trust this helps.

[wael_afifi]

Thank you so much @TMS for providing the formula
And thank you @ORoos for the clarifications.

@ROoos,
After updating the highlighted mistakes in my report as mentioned.
I have one question if i may:
Now after applying the formula i can calculate the number of agents who are scheduled for breaks in the 6:00 AM interval in B11 and so on, right?

[ORoos]

Hi wael_afifi,
Yes TMS's formula is counting the number of agents scheduled for a break for each of the half hour blocks starting from 6AM onwards in row 11.
You have a total of 249 breaks in your sample data. The formula is picking up 249 breaks, hence balancing back.

You have 0 agents on breaks between 6:00 - 6:30 (they just started)
2 between 6:30 - 7:00
2 between 7:00 - 7:30 . . .
2 between 9:00 - 9:30
8 between 9:30 - 10:00 etc.