Count the number of employees per hour

[mente73]

Hello and thank you in advance for your help.

I am looking for a formula to count the number of employees staffed by hour.

in other words, I need to know how many employees I have at 6am, 7am, 8am and so on.

Can anyone share your knowledge?

[than_gold]

Try this:

=SUM(COUNTIFS($C$2:$C$49, {"3:00 PM","4:00 PM","5:00 PM"}))

Check here for more info (Formula 2. SUM COUNTIFS with an array constant): https://www.ablebits.com/office-addi...omment-page-4/

[mente73]

thank you for all the information

[GeoffW283]

I'm not sure how you wanted the results presented, so I created a second worksheet "Coverage" - here is portion of the worksheet to show how it looks:

coverage.png

There is a column for each of the seven days of the example you provided and 24 rows - one for each hour. The body of the table is then populated with the number of people covering any particular hour via the following formula in B2 copied across and down:

=SUMPRODUCT(((OFFSET(Times!$B$3, 0, MATCH(B$1, Times!$B$1:$O$1, 0)-1, COUNTA(Times!$A:$A)-1, 1)<=$A2)*

(OFFSET(Times!$B$3, 0, MATCH(B$1, Times!$B$1:$O$1, 0), COUNTA(Times!$A:$A)-1, 1)>$A2)*

(OFFSET(Times!$B$3, 0, MATCH(B$1, Times!$B$1:$O$1, 0), COUNTA(Times!$A:$A)-1, 1)>= OFFSET(Times!$B$3, 0, MATCH(B$1, Times!$B$1:$O$1, 0)-1, COUNTA(Times!$A:$A)-1, 1))) +



(NOT((OFFSET(Times!$B$3, 0, MATCH(B$1, Times!$B$1:$O$1, 0)-1, COUNTA(Times!$A:$A)-1, 1)>$A2)*

 (OFFSET(Times!$B$3, 0, MATCH(B$1, Times!$B$1:$O$1, 0), COUNTA(Times!$A:$A)-1, 1) <=$A2))*

 (OFFSET(Times!$B$3, 0, MATCH(B$1, Times!$B$1:$O$1, 0), COUNTA(Times!$A:$A)-1, 1) <  OFFSET(Times!$B$3, 0, MATCH(B$1, Times!$B$1:$O$1, 0)-1, COUNTA(Times!$A:$A)-1, 1)))  )

The formula is repetitious rather than complex although it doubled in size due to the need to accommodate shifts that span midnight.

See the attached workbook which implements the above.

Let me know if this is close to what you are looking for.

[mente73]

GeoffW283, this is exactly what I was looking for. Can't thank you enough, much appreciated.

[GeoffW283]

Glad to help - thanks for the feedback!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[mente73]

Hello GeoffW283, hope you are doing great.

Some time ago, you helped me out with this request to be able to count the number of agents in schedule per hour however didn't use it that much.
Now, I do need to use it more often but I just realized that the spreadsheet provided doesn't count that well overnight agents.


Using the below scheduled hours for those 12 agents, I am coming up with 6 agents at 12:00:00 however we have 10 agents in schedule after 12 MN

Oct 7th

12:30 AM 6:30 AM
12:30 AM 6:30 AM
6:00 PM 12:00 AM
6:00 PM 12:00 AM
9:00 PM 3:00 AM
7:00 PM 1:00 AM
8:00 PM 2:00 AM
OFF OFF
7:00 PM 1:00 AM
12:30 AM 6:30 AM
7:00 PM 1:00 AM
12:30 AM 6:30 AM
9:00 PM 3:00 AM

Can you give me a hand once more?
I really appreciate it.

Mente73

I have attached the spreadsheet with sample information.

[JeteMc]

This array entered formula** seems to yield values that seem reasonable based on the data:

=IF($A2<12/24,SUM(COUNTIFS(INDEX(Times!$B$3:$N$49,,AGGREGATE(15,6,(COLUMN($B$1:$N$1)-COLUMN($A$1))/(Times!$B$1:$N$1=B$1)/(Times!$B$2:$N$2="IN"),1)),">="&$A2,INDEX(Times!$B$3:$N$49,,AGGREGATE(15,6,(COLUMN($B$1:$N$1)-COLUMN($A$1))/(Times!$B$1:$N$1=B$1)/(Times!$B$2:$N$2="IN"),1)),"<="&SUM($A2,59/1440)),COUNTIFS(Times!C$3:C$49,">="&$A2,INDEX(Times!$C$3:$O$49,,AGGREGATE(15,6,(COLUMN($C$1:$O$1)-COLUMN($B$1))/(Times!$B$1:$N$1=B$1)/(Times!$C$2:$O$2="OUT"),1)),"<="&SUM($A2,59/1440)),SUMPRODUCT((IFERROR(INDEX(Times!$B$3:$N$49,,AGGREGATE(15,6,(COLUMN($B$1:$N$1)-COLUMN($A$1))/(Times!$B$1:$N$1=B$1)/(Times!$B$2:$N$2="IN"),1))-(INDEX(Times!$B$3:$N$49,,AGGREGATE(15,6,(COLUMN($B$1:$N$1)-COLUMN($A$1))/(Times!$B$1:$N$1=B$1)/(Times!$B$2:$N$2="IN"),1))>INDEX(Times!$C$3:$O$49,,AGGREGATE(15,6,(COLUMN($C$1:$O$1)-COLUMN($B$1))/(Times!$B$1:$N$1=B$1)/(Times!$C$2:$O$2="OUT"),1)))<$A2,FALSE))*(INDEX(Times!$C$3:$O$49,,AGGREGATE(15,6,(COLUMN($C$1:$O$1)-COLUMN($B$1))/(Times!$B$1:$N$1=B$1)/(Times!$C$2:$O$2="OUT"),1))>$A2))),SUM(COUNTIFS(INDEX(Times!$B$3:$N$49,,AGGREGATE(15,6,(COLUMN($B$1:$N$1)-COLUMN($A$1))/(Times!$B$1:$N$1=B$1)/(Times!$B$2:$N$2="IN"),1)),">="&$A2,INDEX(Times!$B$3:$N$49,,AGGREGATE(15,6,(COLUMN($B$1:$N$1)-COLUMN($A$1))/(Times!$B$1:$N$1=B$1)/(Times!$B$2:$N$2="IN"),1)),"<="&SUM($A2,59/1440)),COUNTIFS(INDEX(Times!$C$3:$O$49,,AGGREGATE(15,6,(COLUMN($C$1:$O$1)-COLUMN($B$1))/(Times!$B$1:$N$1=B$1)/(Times!$C$2:$O$2="OUT"),1)),">="&$A2,INDEX(Times!$C$3:$O$49,,AGGREGATE(15,6,(COLUMN($C$1:$O$1)-COLUMN($B$1))/(Times!$B$1:$N$1=B$1)/(Times!$C$2:$O$2="OUT"),1)),"<="&SUM($A2,59/1440)),SUMPRODUCT((INDEX(Times!$B$3:$N$49,,AGGREGATE(15,6,(COLUMN($B$1:$N$1)-COLUMN($A$1))/(Times!$B$1:$N$1=B$1)/(Times!$B$2:$N$2="IN"),1))<$A2)*(IFERROR(INDEX(Times!$C$3:$O$49,,AGGREGATE(15,6,(COLUMN($C$1:$O$1)-COLUMN($B$1))/(Times!$B$1:$N$1=B$1)/(Times!$C$2:$O$2="OUT"),1))+(INDEX(Times!$B$3:$N$49,,AGGREGATE(15,6,(COLUMN($B$1:$N$1)-COLUMN($A$1))/(Times!$B$1:$N$1=B$1)/(Times!$B$2:$N$2="IN"),1))>INDEX(Times!$C$3:$O$49,,AGGREGATE(15,6,(COLUMN($C$1:$O$1)-COLUMN($B$1))/(Times!$B$1:$N$1=B$1)/(Times!$C$2:$O$2="OUT"),1)))>$A2,FALSE)))))

Let us know if you have any questions.

EDIT: **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[mente73]

Hello JeteMc,

Thank you for taking the time to review and provide assistance.

Now, it does look more accurate however you think that we should consider the agents OUT at 12 being part of that interval?

I mean if I leave at 12am at 12:01 I no longer belong to that interval

What are your thoughts?

Thanks you again

Mente73

[JeteMc]

To meet that condition try modifying the array entered formula (see edit in post #9) to read as follows:

=IF($A2<12/24,SUM(COUNTIFS(INDEX(Times!$B$3:$N$49,,AGGREGATE(15,6,(COLUMN($B$1:$N$1)-COLUMN($A$1))/(Times!$B$1:$N$1=B$1)/(Times!$B$2:$N$2="IN"),1)),">="&$A2,INDEX(Times!$B$3:$N$49,,AGGREGATE(15,6,(COLUMN($B$1:$N$1)-COLUMN($A$1))/(Times!$B$1:$N$1=B$1)/(Times!$B$2:$N$2="IN"),1)),"<="&SUM($A2,59/1440)),COUNTIFS(Times!C$3:C$49,">"&$A2,INDEX(Times!$C$3:$O$49,,AGGREGATE(15,6,(COLUMN($C$1:$O$1)-COLUMN($B$1))/(Times!$B$1:$N$1=B$1)/(Times!$C$2:$O$2="OUT"),1)),"<="&SUM($A2,59/1440)),SUM((IFERROR(INDEX(Times!$B$3:$N$49,,AGGREGATE(15,6,(COLUMN($B$1:$N$1)-COLUMN($A$1))/(Times!$B$1:$N$1=B$1)/(Times!$B$2:$N$2="IN"),1))-(INDEX(Times!$B$3:$N$49,,AGGREGATE(15,6,(COLUMN($B$1:$N$1)-COLUMN($A$1))/(Times!$B$1:$N$1=B$1)/(Times!$B$2:$N$2="IN"),1))>INDEX(Times!$C$3:$O$49,,AGGREGATE(15,6,(COLUMN($C$1:$O$1)-COLUMN($B$1))/(Times!$B$1:$N$1=B$1)/(Times!$C$2:$O$2="OUT"),1)))<$A2,FALSE))*(INDEX(Times!$C$3:$O$49,,AGGREGATE(15,6,(COLUMN($C$1:$O$1)-COLUMN($B$1))/(Times!$B$1:$N$1=B$1)/(Times!$C$2:$O$2="OUT"),1))>$A2))),SUM(COUNTIFS(INDEX(Times!$B$3:$N$49,,AGGREGATE(15,6,(COLUMN($B$1:$N$1)-COLUMN($A$1))/(Times!$B$1:$N$1=B$1)/(Times!$B$2:$N$2="IN"),1)),">="&$A2,INDEX(Times!$B$3:$N$49,,AGGREGATE(15,6,(COLUMN($B$1:$N$1)-COLUMN($A$1))/(Times!$B$1:$N$1=B$1)/(Times!$B$2:$N$2="IN"),1)),"<="&SUM($A2,59/1440)),COUNTIFS(INDEX(Times!$C$3:$O$49,,AGGREGATE(15,6,(COLUMN($C$1:$O$1)-COLUMN($B$1))/(Times!$B$1:$N$1=B$1)/(Times!$C$2:$O$2="OUT"),1)),">"&$A2,INDEX(Times!$C$3:$O$49,,AGGREGATE(15,6,(COLUMN($C$1:$O$1)-COLUMN($B$1))/(Times!$B$1:$N$1=B$1)/(Times!$C$2:$O$2="OUT"),1)),"<="&SUM($A2,59/1440)),SUM((INDEX(Times!$B$3:$N$49,,AGGREGATE(15,6,(COLUMN($B$1:$N$1)-COLUMN($A$1))/(Times!$B$1:$N$1=B$1)/(Times!$B$2:$N$2="IN"),1))<$A2)*(IFERROR(INDEX(Times!$C$3:$O$49,,AGGREGATE(15,6,(COLUMN($C$1:$O$1)-COLUMN($B$1))/(Times!$B$1:$N$1=B$1)/(Times!$C$2:$O$2="OUT"),1))+(INDEX(Times!$B$3:$N$49,,AGGREGATE(15,6,(COLUMN($B$1:$N$1)-COLUMN($A$1))/(Times!$B$1:$N$1=B$1)/(Times!$B$2:$N$2="IN"),1))>INDEX(Times!$C$3:$O$49,,AGGREGATE(15,6,(COLUMN($C$1:$O$1)-COLUMN($B$1))/(Times!$B$1:$N$1=B$1)/(Times!$C$2:$O$2="OUT"),1)))>$A2,FALSE)))))

Let us know if you have any questions.

[mente73]

Thanks JetMec,

I assume the new array is for the coverage spreadsheet, what about for the hr intervals included in the time spreadsheet? Columns T to AA?

[JeteMc]

I forgot to delete T2:AA26 on the Times sheet before attaching the file to post #9.
The formula in those cells represents an earlier draft of the formula shown in the post.

[mente73]

Alright,

Thank you enough, the new Array seems to be working as expected.

Much appreciate it.

Mente73

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.