COUNTIFS - am I using it correctly?

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

Please excuse my ignorance - my settings must have changed as I could not see the upload attachment button anywhere and I thought it may have been removed from the forum.

I have now uploaded a smaller version of my spreadsheet (all Macros have been removed). The Leave Planner starts out with 14 members of staff at 1 Jan 2015 and 18% of these are allowed time off at any one time, equating to 2.52 allowed off (which is rounded up to 3). Now, 3 people leave the section on 5/1/15, leaving 11 in total (from 5/1/15). This means that the number allowed on leave falls to 1.98 (rounded up to 2). My problem is that I can get the correct figure to display on the 5/1/15, but anything after then reverts back to 2.52. The formula I have at Row 24 is:

=COUNTIFS($D32:$D53,"AO",$E32:$E53,"<>"&T$30)/100*T8

which, as I have said, does give me the correct result, but only for the 1 day. Everything after 5/1/15 also needs to be showing as 1.98.

Regards

Paul S

Richard,

Many thanks for your reply. I have tried your formula as well and it gives the same results as Quang PT. Please see my reply to Quang PT.

Regards

Paul S

Hi Quang PT

Many thanks for your reply. I thought it had done it; however, if someone else leaves the section on, say 8/1/15, then the number goes up from 1.98 to 2.34 from 8/1/15 onwards - I would have thought the number would have been less?

Thanks

Paul S

Hi Quang PT

Your message just came in as I was thanking Richard (and your first message). This solution works as well.

Many thanks indeed

Regards

Paul S