Forum Discussion
Rodney2485
Jan 20, 2025Brass Contributor
Count only Scheduled Employee's
I have a spread sheet of employee's and schedules.
I need to be able to see how many are scheduled today, vs how many actually showed.
Currently i'm using
=COUNTIFS('Active Associates'!$G:$G,"1st",'Active Associates'!$E:$E,"Full TIme")
However I have employee's who fall under a different employment term i.e Temp & Seasonal. I also have a weekend shift that overlaps twice a week with 1st shift on Monday & Friday
Ideally what I want is to be able to count all the people who are scheduled for any giving day of the week based on the current date, count how many are absent and then calculate total percentage on site.
Hopefully the attached file makes more sense.
I think we're on the right path and i'm just having trouble putting all the pieces together.
The issue i'm having now is only counting the people scheduled for "Today" as indicated by the Day code in Q4
I've attached your previous file with different attempts at getting this to work.
- Rodney2485Brass Contributor
Here's where i'm currently stuck at.
I've highlighted the cells in yellow. I'm trying to show that on Tuesday (Today) that I should have 4 people on site. The rest of the day's should be blank or "0" because it's not Tuesday.I've got the date, and a Day code up top to help but i'm just not able to calculate it correctly.
- NnyiimockBitanyanmiBrass Contributor
I think the workbook here helps. If not, then give more insight and let me dig deep. Thank you
- NnyiimockBitanyanmiBrass Contributor
The workbook below can give you a more directed approach to your solution. Thus, if i understand you. I have added the counts of those that overlap with 1st and also provided another row that calculates the Temp&Seasonal aspects. Well, you can give more insight as to what you need if this does not fit your requirement
- m_tarlerBronze Contributor
It isn't very clear what exactly you need but I THINK what you need is to convert the schedule text into an actual list of days so you can compare any given to that list. In the attached I created a LAMBDA function that can do this for you so then you just need to compare to see if any given day is in that list. If this isn't what you need, maybe you could explain more.
- Rodney2485Brass Contributor
I think we're on the right path and i'm just having trouble putting all the pieces together.
The issue i'm having now is only counting the people scheduled for "Today" as indicated by the Day code in Q4
I've attached your previous file with different attempts at getting this to work.
- m_tarlerBronze Contributor
I think all you needed to do was add parantheses so that product is inside the SUMPRODUCT like this:
=SUMPRODUCT(('Active Associates'!G:G=Sheet2!B3)*('Active Associates'!H:L=Sheet2!Q4))