Multiple IF's formula Help

[Scott_T_27]

Hi All

I'm wondering if someone can help please? I will have to explain sheet as I'm on work PC so unable to upload example spreadsheet.

I need a formula that will calculate/count how many holidays someone needs based on their shift pattern. Warehouse is open 7 days a week and workers are split between 4 different teams with different days off.

In column A I have the date, Columns B to E, teams 1 to 4. Which then states if that team is "IN" or "OFF" on that particular day.

All workers details are then logged on a separate tab.

On a further tab I then have a VLOOKUP that if I input the workers payroll into column A it will populate columns B and C with Shift (Days, Evenings, Nights) and Team (1-4) from workers details tab.
In cells D and E I have "Holiday Start" and "Holiday End" which will be populated manually based on the holiday request.
Then in cell F is where I need a formula to tell me how many "Days Off Required"
So I would like it to look at the date range on the other sheet and count the IN's if possible.

Hope all that makes sense.

Any and all help greatly appreciated, I just can't seem to make it work.

TIA

Scott T

 

[steve the fish]

That should be straightforward with a COUNTIFS formula but its not possible to help much further without much detail on your setup.

 

[Scott_T_27]

What further detail do you need?
I thought I had explained it all above?

Thanks

 

[steve the fish]

This isnt clear to me at least:

In column A I have the date, Columns B to E, teams 1 to 4. Which then states if that team is "IN" or "OFF" on that particular day.

Is that a date for every day of the year in column A? Then each date has been populated with an IN or OFF for each team?

How does this:

the workers payroll

relate to the workers team? Cant see how you get to the team column from the payroll number.

 

[Scott_T_27]

Sorry, yes column A has the date in for every day of the year. Then each days says either IN or OFF for each team.

On a separate sheet there is a full list of all workers with their payroll, shift, name, team, etc.

On a third sheet is when I want the results displayed. If I input the workers payroll it then populates shift and team cells using a VLOOKUP.

Hope that makes sense lol. I know it should be easy but looking at it so long and I can't figure it out.

 

[steve the fish]

Ok copy and paste the following into A1:

[TABLE="width: 534"]
<colgroup><col><col span="6"><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Shift1[/TD]
[TD]Shift2[/TD]
[TD]Shift3[/TD]
[TD]Shift4[/TD]
[TD][/TD]
[TD]Date Start[/TD]
[TD="align: right"]11/02/2019[/TD]
[/TR]
[TR]
[TD="align: right"]01/02/2019[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD][/TD]
[TD]Date End[/TD]
[TD="align: right"]16/02/2019[/TD]
[/TR]
[TR]
[TD="align: right"]02/02/2019[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD]Shift[/TD]
[TD]Shift1[/TD]
[/TR]
[TR]
[TD="align: right"]03/02/2019[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD]No. Req[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]04/02/2019[/TD]
[TD]IN[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05/02/2019[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]06/02/2019[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]IN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]07/02/2019[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]08/02/2019[/TD]
[TD]OFF[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09/02/2019[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10/02/2019[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11/02/2019[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12/02/2019[/TD]
[TD]IN[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13/02/2019[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14/02/2019[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]IN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2019[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16/02/2019[/TD]
[TD]OFF[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17/02/2019[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]18/02/2019[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19/02/2019[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20/02/2019[/TD]
[TD]IN[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]21/02/2019[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22/02/2019[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]IN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23/02/2019[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24/02/2019[/TD]
[TD]OFF[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25/02/2019[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]26/02/2019[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]27/02/2019[/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28/02/2019[/TD]
[TD]IN[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The formula in H4 is:

=COUNTIFS(A:A,">="&H1,A:A,"<="&H2,INDEX(B:E,,MATCH(H3,B1:E1,0)),"IN")

 

[steve the fish]

You may have to change the dates to suit your locale. Im in UK so use date format DD/MM/YYYY

 

[Scott_T_27]

Mate that is great thank you. I'm in UK too.

I'll give it a whirl now, thanks again.