Prioritising countif statements

Hi All,

This is a little long winded but please bear with me.

I have a roster I have developed, but for simplicity reasons, and the need to take away Human Error I am trying to have this roster show when there is a coverage issue across 1 of the 3 departments (Prod, D&B, Survey). I currently have countifs which determine if there is less than 3 personnel on site or if there is no one able to provide coverage for one or more of the departments on any given day. See the spreadsheet below;

Roster Problem.xlsx

While this works well for days 7 and 8, for example, Day 12 should also return a "CHK" Message. Whilst there is 3 personnel on site and there is someone that can cover each of the departments, it is in fact the same person (Tim) who is able to perform both Prod and Survey Duties. However, 1 person can only perform one duty and thus he should be discounted from survey as he will be required to perform the prod role.

So my question is, how can I adjust this formula to take into account situations such as Day 12, where there will be no coverage for either the prod or survey roles?

Thanks for your help in advance,

Cheers,

Spicey

The question is how to adjust the formula I have to take into account that one person can only perform one job? So take day 12 for example, Tim is competent in performing both Prod and Survey jobs but he can only do one. SO, If he does Prod, then there is no one to do Survey, or, if he does Survey, there is no one to do Prod, even though there are another 2 people at work. (Those two people can only perform D&B work).

I want my Coverage Check formula to take these situations into consideration. At the moment it says OK for day 12 but as I have explained, There will not be enough people available. I suspect it might require a Macro, or multiple IF statements, I am not sure.

Cheers

Looks like you need a macro with a loop that would first assign people that can cover only one department and then those who can cover more than one. Still thinking if the same can be achieved with several formulas but macro would seem to be your best bet.

Ok I was thinking/fearing that would be the case. I'm still trying to work out a way of using formulas, but coming to a dead end. I would like to avoid the use of a macro if possible.

This is a very ugly attempt at what you need but should do the trick. The line that you should be looking at is row 45.