Counting multiple groups of employees present every half hour (can't get formula to work)

Hi everyone,

I'm new here as I'm sure you can tell.

So here's my problem that I'm trying to solve.

it's actually a multiple step issue.

So problem #1 where I work I have multiple employees I'm in charge of and they all have different shifts.
So I'm trying to calculate how many employees I have every half hour.
I have each grouping of employees with their time in and time out, however I also have what's called a "CODE".
If code is 01 Vacation, 02 Sick, FMLA, MILL military, these are types of leave. So if it says 01, 02, FMLA, MILL, I need it to not count the employee as present.

I also will not be subtracting a lunch 30 minutes of their shift is accounted as a lunch and they can be sent at different times.

I also have the issue that any name in BOLD is a shift lead. So I'm counting my employees and leads per half hour in my time table

TIME Employees Leads
12:00
12:30

My last question is an odd one that might not have an answer, I've seen people print out rosters from my job from excel that has a shift that shows between 13-17 employees and it fits in the same space. (Example 1-17 on bottom right employee list. Sometimes it will have 13 employees listed and sometimes it will have 17 but it always takes up the same space when printed.)

Anyway thanks for any help you can provide.

Formula:

Please Login or Register  to view this content.

Now I get this formula above to work fine, however I can't get it to do a whole set let alone multiple sets. when I try the code below I get a #VALUE!

Formula:

Please Login or Register  to view this content.

Microsoft office 2016

Copy of work book on Google Docs .xlsx file

Alright sorry for the time between posts, I've been quite busy at work.

I've gone ahead and changed the names to something more singular for persons but not my employee names.

So I have to account for how many Shift Leads I have and how many employees I have.

I've decided it would be easier to have some hidden columns that have a simple Yes/No for LEAD?

This way you can do a simple If yes then 1 lead.

As far as shift times they are usually reliable, however we sometimes have people come in earlier or later so I need a formula which calculates for employees,
if they are here from 1400-1900 or is I change it to 1200-1700.

I print this sheet for my shifts so BOLD stands for a shift Lead and pink/blue Girls/Guys.

My Goal is to know how many employees/leads I have every 30 minute increment of the day.
From 1200-2230 (duration of 2nd shift)

Anyway if you have a better idea on how to implement the Lead/employee or can help with the formula to calculate how many employees and Leads are there every 30 minutes it would be very appreciated.

So the goal I have is as follows.

Is take all the employees listed and their times in and out and calculate how many people I have.

And also subtract people out who aren't here.

Everyone from F6-F19, L6-L12, L18-L27, R3, R7-R9, R12-R22, time in vs time out vs Lead or not? and is 01, 02, MILL, FMLA mark them as not here.

I want it to automatically count how many people are here like below. I can do it by hand but it would be easier to have a formula to do this.

so times as follows.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

CNTRL + SHIFT + ENTER while in the formula bar returns 14. So It's counting the 14 People but not subtracting the 2 on 01.

and I also need to work in the LEAD? A6-A19, G6-G12, G18-G27, M3, M7-M9, M12-M22. essentially If Lead = YES I want it to count in the LEAD tab instead of employee tab. The above time table is how I would like it to list out the data on the table.

If the Code is equal to 01, 02, FMLA, MILL then add 0 to the employee or LEAD, if left blank count 1 if the employee is in their working hours range.

So if you work 1145-2015 then you will be counted in the 2000 category but not the 2030 because you have gone home.