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.

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

<tbody>
</tbody>

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