Count the number of employees working per hour

Hi, welcome to trhe forum

Have you looked at using the COUNTFS() function?

If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

I've tried the Countif but maybe I'm doing something wrong.

I actually sai countifS().

countif() allows 1 criteria, countifS() allows for multiple

OK try this, based on your sample workbook, copied down and across...
=COUNTIFS(OFFSET($A$4,MATCH(R$5,$A$5:$A$11,0),1,1,14),">="&$Q6,OFFSET($A$4,MATCH(R$5,$A$5:$A$11,0),1,1,14),"<"&$Q7)

copy that to R6

Then copy it down as far as you need it, and copy it across for all names. In other words, copy it into your entire (2nd) table

hmm your profile saya excel 2000 but your uploaded file is .xlsx - 2007 or later. What version are you actually using?

OK countifS() only works in 2007 and later. Try this instead...
=SUMPRODUCT(--(OFFSET($A$4,MATCH(R$5,$A$5:$A$11,0),1,1,14)>=$Q6),--(OFFSET($A$4,MATCH(R$5,$A$5:$A$11,0),1,1,14)<$Q7))

I would throw it into an interval calendar. I just built one real quick you can expand it to be in 15 min intervals which would double the width of the table... currently in Half hour intervals. Now if you do I would group into the half hour so that it is not so wide when browsing...

Take a look, this is how I would setup a schedule check if I was making one... Then you can extend it to add lunches and breaks accordingly...

Book2_ScheduleExample.xlsx

I would also throw in some CF (Conditional Formatting) to better illustrate visually what is happening on a given day..

I suppose I should explain too...
To start off, I pull in the Start/End times based on the day selected on the IntervalCount tab

Please Login or Register  to view this content.

This is just doing a VLookup match, you will notice I added header info to your Schedule tab for this process.

Then I listed out each interval in 30 minute increments. Once you have that setup, the table can be filled with the following formula

Starting In F5 and then repeated down and across.

Please Login or Register  to view this content.

What is happening?

First I check the start time C5 to see if the person is ON that day.
If they are OFF it will display O all the way across
IF they are on I must first check to see if they are grave or day shift.

This is done by checking if the End time is Less than the start time
Now that we know that we do one of two things

For grave shifts I want to know if the End time is less than or equal to the current interval and the Start time is Greater than the current interval.
If that is true than it should be blank as that means they are not on, otherwise place an X

For day shift I want to know if the Start time is less than or equal to the current time and that the end time is greater than the current time. This means they are on so again lets place an X to indicate they are here and than leave it blank if they are not.

Now up top I can do a count of X in the column to say how many are on in a given interval, adding 2 half hours/2 to get the total per hour.

Let me know if you have questions about anything I put in there that I did not explain -

This is not the only way to set it up, nor does it means it is the right way for you... but this is definitely the route I would take for scheduling anything

Enjoy

Well probably cause I somewhat made it up based on need...
But now that you have heard... And seen!
Is this beneficial and/or does it help for what you are using counts for... or does this go beyond what you need?

Not sure if you were saying... "I don't like it don't want it" or... "I Don't know what this is'

Did the file open for you?

Was eating and typing - you already responded - Glad it will do the trick... Enjoy - and yea this Forum is pretty good that way. Be sure to contribute some knowledge on anything you see open if you feel you have a good method or thought to add!

And don't forget to set this thread to Solved under Thread Tools