I am trying to get my head around the logic of the COUNTIFS formula for the following example. (Abridged copy of) spreadsheet attached for convenience - all macros removed.
I have a staff leave planner which counts all instances of "A" in the planner area and determines whether too many people are on leave at any one time (based against 18% of staff on leave at any one time). Row 10 shows how many staff have booked leave (counting all the "A"s in each particular column and Row 11 shows how many can be on leave. If leave is oversubscribed, then Row 10 (relevant column) turns red.
So, my fly in the ointment is this. If someone leaves the section, a date is put in Column E. When this date is put in, the leave chart turns grey for the relevant row from the date they left; however, I would like the "A" to still remain on the leave chart (in case someone questions it later as the person will more than likely move internally). What I don't want though, is for any leave they have from the date they left to be counted towards those allowed off. So, although I want to count all the "A"s, I don't want to count the "A"s for someone who has left (from the date they left).
In the example I have attached, I need to have U10 show '3', V10 to show '3' and W10 to show '2'. I have a very simple COUNTIFS formula in Row 10, but is not giving the correct figures. I have tried countless COUNTIFS and COUNTIF together (see formula at Row 12), but am driving myself mad trying to work it out.
Hope I've made it reasonably clear to understand and thanks for reading.
ps. Dates in UK format dd/mm/yyyy
Paul S
I have a staff leave planner which counts all instances of "A" in the planner area and determines whether too many people are on leave at any one time (based against 18% of staff on leave at any one time). Row 10 shows how many staff have booked leave (counting all the "A"s in each particular column and Row 11 shows how many can be on leave. If leave is oversubscribed, then Row 10 (relevant column) turns red.
So, my fly in the ointment is this. If someone leaves the section, a date is put in Column E. When this date is put in, the leave chart turns grey for the relevant row from the date they left; however, I would like the "A" to still remain on the leave chart (in case someone questions it later as the person will more than likely move internally). What I don't want though, is for any leave they have from the date they left to be counted towards those allowed off. So, although I want to count all the "A"s, I don't want to count the "A"s for someone who has left (from the date they left).
In the example I have attached, I need to have U10 show '3', V10 to show '3' and W10 to show '2'. I have a very simple COUNTIFS formula in Row 10, but is not giving the correct figures. I have tried countless COUNTIFS and COUNTIF together (see formula at Row 12), but am driving myself mad trying to work it out.
Hope I've made it reasonably clear to understand and thanks for reading.
ps. Dates in UK format dd/mm/yyyy
Paul S
