• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Correct use of COUNTIFS and COUNTIF [SOLVED]

P

paulstan

New Member
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
 

Attachments

  • ssheetPlanner.xlsm
    133.1 KB · Views: 9
Hi Paul,

Use this formula in P10 and copy right.

=SUMPRODUCT((P17: P53="A")*($D$17:$D$53="SEO"))-SUMPRODUCT((P17: P53="A")*($D$17:$D$53="SEO")*(P15>=$E$17:$E$53)*NOT(ISBLANK($E$17:$E$53)))

Regards,
 
Last edited by a moderator:
Your formula in U10
=COUNTIFS(U17:U53,"A",$D17:$D53,"SEO",$E17:$E53,"<>""")

I think should be
=COUNTIFS(U17:U53,"A",$D17:$D53,"SEO",$E17:$E53,"<>"&"")
Which returns 2 as there are 4 x A's in Column U, but only 2 values in Column E which don't have values
 
Hui said:
Your formula in U10
=COUNTIFS(U17:U53,"A",$D17:$D53,"SEO",$E17:$E53,"<>""")

I think should be
=COUNTIFS(U17:U53,"A",$D17:$D53,"SEO",$E17:$E53,"<>"&"")
Which returns 2 as there are 4 x A's in Column U, but only 2 values in Column E which don't have values
Click to expand...


Hi Hui

Many thanks for this. If I use the formula you provided this gives me a correct value in Column W (2), but incorrect values in Columns U & V (2). U & V should be showing 3 as that is how many blue A's there are in those columns.

Thanks

Paul S
 
Somendra Misra said:
Hi Paul,

Use this formula in P10 and copy right.

=SUMPRODUCT((P17: P53="A")*($D$17:$D$53="SEO"))-SUMPRODUCT((P17: P53="A")*($D$17:$D$53="SEO")*(P15>=$E$17:$E$53)*NOT(ISBLANK($E$17:$E$53)))

Regards,
Click to expand...
Hi Somendra,

Many thanks for this formula; however, after copying it in, it produces a TRUE value only.

Regards

Paul S
 
Hui said:
What logic is there for the A's being blue ?
Click to expand...
Hui

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
 
Hui said:
U10: =SUMPRODUCT((U$17:U$53="A")*($E$17:$E$53<=U$15))
Click to expand...
Hui

Many thanks for this. It nearly worked. Columns U,V,W are showing correctly; however, Column X is showing as only 1 on leave, whereas there are 2 blue A's. The peson at Row 22 does not leave the section until 12/01/15 so any leave before this date (in this case 09/01/15) should be counted. Also, if you remove the white A from Row 23 for 06/1/15, Row 10 reverts to 2 on leave, where there are 3 blue A's.

Also which needs to be factored into the formula is the fact that there will be 4 different grades (SEO being one of them), so this would need to be shown as well.

Thanks

Paul S
 
paulstan said:
Hi Somendra,

Many thanks for this formula; however, after copying it in, it produces a TRUE value only.

Regards

Paul S
Click to expand...

Hi Somendra,
Please accept my apologies. After revisiting your formula, it now works as it should do. Amazing.

Extremely grateful.

Thanks

Paul S
 
paulstan said:
Hui

Many thanks for this. It nearly worked. Columns U,V,W are showing correctly; however, Column X is showing as only 1 on leave, whereas there are 2 blue A's. The peson at Row 22 does not leave the section until 12/01/15 so any leave before this date (in this case 09/01/15) should be counted. Also, if you remove the white A from Row 23 for 06/1/15, Row 10 reverts to 2 on leave, where there are 3 blue A's.

Also which needs to be factored into the formula is the fact that there will be 4 different grades (SEO being one of them), so this would need to be shown as well.

Thanks

Paul S
Click to expand...

Hui

After revisiting Somendra's post, I managed to finally get the thing to work properly.

Many thanks for your help. It has been most valuable and certainly opened my eyes to some of the more complex formulas.

Regards

Paul S
 
You must log in or register to reply here.
Back
Top