Tricky calculation using formulas in Excel

Question

I try to calculate the sum of all possible shifts a person can take during a period based on availability:

Assume empty is equivalent to available in scheme number 2.

This is what I got so far:

=SUMPRODUCT(($B$14:$B$15=$C$2)*$C$14:$E$15)

Hence, I obtain:

This should be the final result:

I still have to somehow check if an employee is available a given day before adding the number to the sum.

And Tuesday, where there is need two employees for shift blue, it should only count one of them.

Update

Thanks for asking. Here is an explanation of the result:

CT can't work Monday, can work Tuesday (empty is equivalent to available), hence 1 blue Tuesday (not possible to cover both blue), and on Wednesday CT can cover 1 red. Summing red gives 1 and summing blue gives 1 for CT's entry.

Da can work 1 red Monday, and 1 red Wednesday and is not available for working blue on Tuesday. Summing red gives 2 and blue gives 0.

JAN can work 1 red Monday and 1 blue Tuesday.

Answer 1

I have the following setup:

In B2 (drag down as needed) I used:

=SUMPRODUCT(--($B7:$D7<>$B$7),--($B$12:$D$12>0))

And in C2 i used:

=SUMPRODUCT(--($B7:$D7<>$B$7);--($B$13:$D$13>0))