# Count the number of staff working between times based on staff position

1. ## Count the number of staff working between times based on staff position

Hi,

I am needing a formula to solve this problem..

I would like to count the number of staff that I have working for each hour of the day based on the individual staff position.

In my current spreadsheet I have a roster of staff that are split into different job types in column E and their start time in column K and finish time in column L.

Also the times of the day start at 7am and finish the following morning at 8am (yes this is 25 hours!!)

I have managed to do a total count using the following formula
=SUMPRODUCT(--((\$K\$7:\$K\$49<(BB17+BC17)/2)+(\$L\$7:\$L\$49>(BB17+BC17)/2)+(\$K\$7:\$K\$49>\$L\$7:\$L\$49)=2))
the cells BB17 and BC17 are the defining times of the day i.e. 07:00 for BB17 and 07:59 for BC17
and this works very well.

The staff positions are listed as numbers from 1-6

Any help would be much appreciated.

3. ## Re: Count the number of staff working between times based on staff position

Here is the file that I am working on, the formula is needed on the SHIFT SHEET tab.

4. ## Re: Count the number of staff working between times based on staff position

This solution puts helper rows in \$X\$52:\$AV\$54 of the Shift Sheet. The first helper row counts Telephonists with the function:
Formula:
`Please Login or Register  to view this content.`
Dispatchers with the function:
Formula:
`Please Login or Register  to view this content.`
Formula:
`Please Login or Register  to view this content.`
Then the range BI17:BK41 with the formula:
Formula:
`Please Login or Register  to view this content.`
*There is no distinction made between TL/SL in rows 8 through 12 so I only filled the SL column.

5. ## Re: Count the number of staff working between times based on staff position

Thanks JeteMc,

I was trying to do this without 'Helper' cells, but this solution does work so I think i will go with this.

Once again many thanks.

6. ## Re: Count the number of staff working between times based on staff position

You're Welcome and thank you for the feedback. If you haven't already, please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope you have a good day.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1