Combining NETWORKDAYS.INTL with IF formula?

I have several employees working in different countries (USA, UK, Germany) so each group has different holidays. I need to calculate how many days it takes to process the order excluding weekends and holidays. In the attached file I created a separate tab called “Holiday list” for each country and I have been using a working formula for each employee.

Formula A: Anna & Karen work in USA so their formula is =NETWORKDAYS.INTL(B2,C2,1’Holiday list’!A2:A9)-1
Formula B: Tami & Keith work in UK so their formula is =NETWORKDAYS.INTL(B2,C2,1’Holiday list’!B2:B8)-1
Formula C: George & Andy work in Germany so their formula is =NETWORKDAYS.INTL(B2,C2,1’Holiday list’!C2:C18)-1

Currently I have to change the formulas weekly based on the employee name (I have to filter based on their names and change manually) so I would like to combine these formulas into one. If column A2 equals Anna or Karen use formula A, if A2 equals Tami or Keith use formula B etc . I have tried nestled IF formulas but can’t get it working. Maybe something else is needed? Thank you.

Hi
Use these

Formula:

Please Login or Register  to view this content.

where 'Days in process'!$J$2:$K$7 is the information of the working country.

See the file Days in process.xlsx

hmmm It's not working. I don't think it likes using the holiday list so yesterday I recreated the formulas for each country by manually retyping the holidays. How would you incorporate this?

Tami & Keith UK formula: =NETWORKDAYS.INTL(B7,C7,1,{"2016/3/25","2016/3/28","2016/5/2","2016/5/30","2016/8/29","2016/12/26","2016/12/27"})-1
Anna & Karen US formula: =NETWORKDAYS.INTL(B2,C2,1,{"2016/1/1","2016/5/30","2016/7/4","2016/9/5","2016/11/24","2016/11/25","2016/12/23","2016/12/26"})-1
George & Andy Germany formula:=NETWORKDAYS.INTL(B9,C9,1,{"2016/1/1","2016/2/8","2016/2/9","2016/2/10","2016/3/25","2016/3/26","2016/3/28","2016/4/4","2016/5/2","2016/5/14","2016/6/9","2016/7/1","2016/9/16","2016/10/1","2016/10/10","2016/12/26","2016/12/27"})-1

Jose's solution works well for me.

Did you look at the book he attached?
It includes a lookup table that you need to create that determines which region each name is in.

I didn’t realize there was a file. However it is not calculating the holidays like it should. I added -1 to formula because if order was processed on the same day the result should be 0. To test the holiday list I amended the dates in the row 12 for Andy. Since Germany has holiday on 2/8, 2/9, 2/10 the result should be 1 and it calculated 4. I also had a question about the formula. Should $C$17 be $C$18? I am stumped.

=NETWORKDAYS.INTL(B2,C2,1,OFFSET('Holiday List'!$A$1:$C$17,1,MATCH(VLOOKUP(A2,'Days in process'!$J$2:$K$7,2,FALSE),'Holiday List'!$A$1:$C$1,0)))-1

Hi all,

I also have to calculate business days for depots KPIs. I have used the formula from Jose (adapted to my data) but it is not working. I get #NUM and # VALUE errors. I don't know if this comes from the cells format...
I am wondering if you would accept to take a look at the file to perhaps identify where the issue comes from. Thanks a lot!