Results 1 to 7 of 7

Thread: Correct use of COUNTIFS and COUNTIF

  1. #1

    Correct use of COUNTIFS and COUNTIF



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,615
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Could you please explain another way? I don't get the logic

  3. #3
    Quote Originally Posted by Pecoflyer View Post
    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. #4

  5. #5
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by paulstan View Post
    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.
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  6. #6
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Simple navic,

    In your web browser, just highlight the topic title, right click and select Google for "Correct....."

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



    http://forum.chandoo.org/threads/cor...-solved.26193/

  7. #7
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by NoS View Post
    In your web browser, just highlight the topic title, right click and select Google for "Correct....."
    Thanks for the advice @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
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

Posting Permissions

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