Sum, Count and maybe IF statement.

Reginald

Hello All,

I’m needing help with creating a formula that seems a bit complex to me.

I think it’s really a two-part formula or more.

My data will span across two different sheets.

My first sheet (Accepted Job) will have data where team members have accepted a Job on a new shift. The important data from this sheet is area, skill class and the new shift.

My second sheet is considered my master or total request count sheet. This sheet has the job capacity limits on it by (area, skill class and requested number of open slots per shift.

I’m not sure how to create a formula that will count, sum, and have IF involved based on the situation.

The formula would need to pull data from (Accepted Job sheet ) based on area, skill, shift  and be able to update the count on Total request sheet. The Total Count Request sheet will eventually be at zero per shift but will need to be offered for all employees in that skill class first.

It should go something this:

Person X works in Block A and falls into the skill class as a 01A and they currently work 2nd shift. Person X wants to work 3rd shift in Block A so they put in a shift preference.

The company has a need for 10 people in the 01A skill class for Block A. Person X will now get the opportunity to go to 3rd shift.

To maintain the right levels of employees the company must subtract 1 person from the 3rd shift request, Block A, 01A skill request sheet.

Request sheet for ( 3rd shift is 10 people) - (1 Person who just accepted the 3rd shift). 9 open slots on 3rd shift.

The company must also update the request on the (Total request sheet) for 2nd shift since person X left their current shift within the Block A 01A skill class.

Since person X left 2nd shift when there were 9 (people) open slots the new request number for the 2nd  shift overall is 10 (people) open slots ( Backfill)

A note of reference: There is a long list of employees who have a shift preference in to go to a different shift. We must go through every person on the list until we have filled all open spots. There are more people than open positions

How do you write a formula to subtract and add based on different criteria’s?

My Need  ( Total Request Count Sheet)

Skill          Area      1st Shift               2nd Shift                 3rd Shift              Total

01A       Block A   4(people)        9 (people)             10 (people)           23

                                                                               

01A       Block B   0(people)        0 (people)                0 (people)            0

The Accepted Job Sheet

Area      Current Shift    Skill Class     New Shift                                  

Block A            1                   01A                   3

Block B            3                   01A                    2

Block C             2                    01A                   1

Any help with this is much appreciated

Thanks

Paul Newcome

Are you able to provide screenshots with sample data for reference?

Reginald

Hi Paul

Thanks for taking a look at this

This sheet will give me the results

This sheet has the information i need to move over in formula or vise versa

As mentioned above as data flows into the accepted sheet ( Source) i need it to update the target sheet based on skill, area and shift all while maintaining a even number based on a certain number of employees i need to go through

Just some context. Here is a Dummy sheet of employes and the shifts they work and the one they are requesting

Once i have gone through this list of employees then i know what my actual need for open positions on my Target sheet.

Paul Newcome

I'm not sure I understand what the difference is between the source and target sheets?

Reginald

Yes I completely understand. It was/is just a confusing for me to trying and explain it over text. I will try to break this down more if possible.

On my source sheet i will have data that will flow in that has many different fields on it including areas, shift, job classes and desired shift that employees have decided that would now like to work. ( in red)

On my target sheet i have certain number of openings for 1st, 2, and 3rd. In this example i will will use this

So i have 3 openings for 1st shift, 8 openings for 2nd and 9 openings for 3rd. Looking back at my accepted offer list. If i was looking to fill 1 of 3 of my open positions on 1st shift i would look at who has a preference in from my accepted sheet. I see i have 1 person i could move to this 1st shift. I then need to update my request numbers on my target sheet for 1st shift. My number now goes from 3 openings to 2 openings. However, because i pulled this employee from 2nd shift to fill my first shift spot, my new request go from 8 openings to 9 openings. The chart may not represent the one for one exactly but that is how this should work.

If the employee meets the skill class, work area and has a preference in for a certain shift , we will need to fill that shift accordingly but also making sure that we backfill the spot we just pulled that employee from. Once we have went through all employees in that skill class, in that work area ,who have requested to be on a different shift and we have fulfilled the open slots on the target sheet we will move on to the new job class and new area. We then repeat the process until all request have been met on the target sheet and we have offered the opportunity to all employees who have a preference in.

If we still have open request on the target sheet at the end of going through the employees that is fine. That tells us we need to go external to hire because we have exhausted all opportunities internally to place employees on those shifts.

Hopes this helps. Looking for a way to automatically count and update the information from my accepted sheet to my target sheet based on area, skill, shift.