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