Google-sheets – Percentage from subtraction

If you don't want to precalculate the subtotal of off days, you can use an array formula:

=arrayformula(SUM((A:A="Jess")*(B:C="OFF")*1))

A:A="Jess" returns a bunch of TRUE and FALSE, in your sample, it would be {TRUE, FALSE, FALSE, TRUE, FALSE}

B:C="OFF" returns another bunch of TRUE and FALSE, in your sample, it would be {FALSE, TRUE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE} (note semicolons denoting the different rows)

And those two multiplied is evaluated as:

From A:A="Jess"  From B:C="OFF"   Result
TRUE             (FALSE, TRUE ) = FALSE TRUE
FALSE            (FALSE, FALSE) = FALSE FALSE
FALSE            (FALSE, FALSE) = FALSE FALSE
TRUE             (FALSE, FALSE) = FALSE FALSE
FALSE            (TRUE , TRUE ) = FALSE FALSE

Only a TRUE multiplied by another TRUE gives a result of TRUE. The *1 last converts every TRUE to 1. SUM then adds all the 1 together.

Your plans seem sensible to me and I think reasonably straightforward to implement. A formula such as:

=importrange("1NsEAt1biV822J4RnqOmb4MW6GCjrkUzfZjbssnZmkSQ)","Sheet1!A1:F8")

in each of the employee sheets in the Director's cut should import Sheet1!A1:F8 from each individual employee's document (once access has been granted). The long string of characters in the middle of the above formula is the 'key' and is unique to each document.

When all the relevant details are in the Director's copy it should then also be quite easy to process them further for analysis purposes.