Forum Discussion
Count only Scheduled Employee's
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.
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.
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.
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))I'm hoping you can help me with this last issue I'm having.
Here's where i'm currently stuck at.
I've highlighted the cells in yellow. I'm trying to show that "Today" I should have 4 people on site. The rest of the day's should be blank or "0" because it's not "Today".I've got the date, and a Day code up top to help but I'm just not able to calculate it correctly.
So I looked at your formula and there was a potential issue with how you were doing the sum (actually COUNTIF). The output of the formula that makes the M,T,W... array will not always put M in column 1 and Tu in column 2, which you realized and had the COUNTIFS + COUNTIFS condition. A more simple and flexible solution is to use that product we did above. I also notice you had selected only certain/relevant groups: "1st","Weekend A","Weekend B" for different days of the week but I don't think that is needed as they are (I think) by definition only applicable for those days of the week so you could use the same group across all days. (i.e. you can't have a "1st" show a "Sa" or a "Weekend A" show a "W"). But if that isn't true you can just delete the incorrect options in those columns. The following formula shows those expected onsite TODAY and the only things that change are the "J$2" as you copy across the columns:
=SUM(ISNUMBER(XMATCH('Active Associates'!$G:$G,{"1st","Weekend A","Weekend B"})) *('Active Associates'!$I:$M=J$2) *('Active Associates'!$F:$F="X") *(J$2=$H$1))So Rows 1 and 2 are the number of people scheduled that day
Row 3 filters / 'ANDs' it with if they are 'on-site'
Row 4 filters / 'ANDs' it with if this column matches today
Thanks for your help, I've got a little more work to do, but I'm confident I can stumble my way through from here. Thank you!