I have attached a screenshot of a leave planner I am in the middle of creating. I have come across one issue I am struggling with and wonder if I am using COUNTIFS correctly at row 24?


Row 8 shows how many staff (by percentage) can be on leave at any one time. Row 24 (which will be hidden) contains the following formula (Column P used in this example):

=COUNTIFS($D32:$D45,"AO",$E32:$E45,"<>"&P$30)/100*P8

So, if a member of staff leaves the section to work elsewhere a date is put into column E and from this date onward (cells are dark greyed out to say they no longer work on the section) I am trying to reduce the number allowed on leave (U24 is showing the correct amount of 1.98). What I am trying to achieve in this example is to have cells V-Z Row 24 also display the value at U24 (1.98).

I need to leave the staff member on the leave planner even if they have left the section, because if they were removed it would affect all percentages across the whole spreadsheet (Row 24).

Row 23 is just Row 24 rounded up.

I've been stuck on this problem for over 2 weeks, so have finally decided to ask for some guidance/help.

Many thanks for viewing.

Paul S

ssheet_Page_1.jpg