How to set up formula in excel

[gdrowell]

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.
[TABLE="width: 500"]
<tbody>[TR]
[TD]NUMBER[/TD]
[TD]NAMES[/TD]
[TD]JOBS[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]BOND[/TD]
[TD]LOADER[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]HARRIS[/TD]
[TD]COVER 1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]SMITH[/TD]
[TD]UNLOADER
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]JOHNSON[/TD]
[TD]SORTER[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]HARRIMAN[/TD]
[TD]COVER 2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]BENSON[/TD]
[TD]COVER 3[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]HAROLD[/TD]
[TD]SORTER[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]FRANCIS[/TD]
[TD]LOADER[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]HENRY[/TD]
[TD]LOADER[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]THOMAS[/TD]
[TD]UNLOADER[/TD]
[/TR]
</tbody>[/TABLE]


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