Countif Formula

Hi All,

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

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

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.

Thank you AliGW

Please find the attached report

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.

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?

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.