# Thread: Correct use of COUNTIFS and COUNTIF

1. ## Correct use of COUNTIFS and COUNTIF

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

2. Could you please explain another way? I don't get the logic

3. Originally Posted by Pecoflyer
Could you please explain another way? I don't get the logic
Here goes!

The blue A's represent staff who are still on the section and their A is counted in Row 10. The white A's are staff who booked leave whilst they were still on the section. When they leave the section, a date is put into Column E and from this date (ownwards) all their blue A's turn to white A's. As they are no longer on the section, their A's should not count against leave booked (row 10) from the date they leave the section. Easiest solution would probably be to delete the individual staff record. Unfortunately, they need to be kept on the leave planner.

For information, Rows 11 & 12 reduce the amount allowed on leave based on number of staff still on the section. This is what I am trying to recreate at Row 10.

Thanks

Paul S

4. Solved

5. Originally Posted by paulstan
Solved
It would be fair of you to share the solution with other forum members here.
To and they know if they have a similar problem.

6. Simple navic,

It's the easiest way to know to ignore most of these cross posters.

7. Originally Posted by NoS
My goal is not that I search the internet for the answer to this question (and also other visitors to this forum, I think).

The man asked for help in this forum.
In the meantime, he solved his problem.
I think it would be fair from him, according to the other members of this forum to set an example of how he solved his problem.

If anything, he should set the URL up with a solution of problem.

We need to be fair to others forum members?
regards

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•