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:

Date	Shift1	Shift2	Shift3	Shift4		Date Start	11/02/2019
01/02/2019	IN	IN	IN	IN		Date End	16/02/2019
02/02/2019	IN	IN	IN	OFF		Shift	Shift1
03/02/2019	IN	IN	OFF	OFF		No. Req	2
04/02/2019	IN	OFF	OFF	OFF
05/02/2019	OFF	OFF	OFF	OFF
06/02/2019	OFF	OFF	OFF	IN
07/02/2019	OFF	OFF	IN	IN
08/02/2019	OFF	IN	IN	IN
09/02/2019	IN	IN	IN	IN
10/02/2019	IN	IN	IN	OFF
11/02/2019	IN	IN	OFF	OFF
12/02/2019	IN	OFF	OFF	OFF
13/02/2019	OFF	OFF	OFF	OFF
14/02/2019	OFF	OFF	OFF	IN
15/02/2019	OFF	OFF	IN	IN
16/02/2019	OFF	IN	IN	IN
17/02/2019	IN	IN	IN	IN
18/02/2019	IN	IN	IN	OFF
19/02/2019	IN	IN	OFF	OFF
20/02/2019	IN	OFF	OFF	OFF
21/02/2019	OFF	OFF	OFF	OFF
22/02/2019	OFF	OFF	OFF	IN
23/02/2019	OFF	OFF	IN	IN
24/02/2019	OFF	IN	IN	IN
25/02/2019	IN	IN	IN	IN
26/02/2019	IN	IN	IN	OFF
27/02/2019	IN	IN	OFF	OFF
28/02/2019	IN	OFF	OFF	OFF

<colgroup><col><col span="6"><col></colgroup><tbody>
</tbody>

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.