## How to set up formula in excel

Hello All,

If anyone can you assist I would be greatly appreciate it.

Ex.: I have a list of 10 Employees that do various jobs. Of those 10 employees, 3 employees cover the job of the person(s) on Vacation. The maximum number employees on vacations is 3.

I would like to show (in a excel Statement), the number of jobs open from vacations, the number of cover employees available, and if any cover person can pass on work. (i.e. 3 Jobs open, 3 cover employees = 0 Passes). However, if a cover employee is on vacation, the cover job doesn't get counted, but the number of cover employees decrease.
 NUMBER NAMES JOBS 1 BOND LOADER 2 HARRIS COVER 1 3 SMITH UNLOADER 4 JOHNSON SORTER 5 HARRIMAN COVER 2 6 BENSON COVER 3 7 HAROLD SORTER 8 FRANCIS LOADER 9 HENRY LOADER 10 THOMAS UNLOADER

To determine who is vacation in any week, the number associated with the name is used in a if statement,

So when Bond is on vacation, i use =IF(ISNUMBER(F2),VLOOKUP(F2,\$A\$2:\$C\$12,2,FALSE),"")

I count the number of jobs on vacation buy using a =CountA

This counts as 1 Jobs open, 3 cover driver= 2 Passes

Where i am having the trouble is when a cover person is on vacation
So if Bond, Harriman (COVER), and Thomas are on vacation, the statement should read

2 Jobs open, 2 cover driver= 0 Passes

(only 2 jobs open, because the cover employee, doesn't need to be covered)

I would like to use a formula that doesn't matter how many jobs, or covers employees i have.

Any help is appreciated. Thanks