Results 1 to 3 of 3

Thread: Count blanks, in a range defined by criteria outside range

  1. #1

    Count blanks, in a range defined by criteria outside range



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

    I am trying to create a sheet to calculate available resources week by week based on a gant chart, I have simplified the sheet below-


    A B C D E F G H I J K L
    1 Week 22 Week 23
    2 Name Job role 30/05 31/05 01/06 02/06 03/06 06/06 07/06 08/06 09/06 10/06
    3 John PM Hol Hol Hol
    4 Steve PM
    5 Stuart BA
    6 Andrew PM Off site Off site Off site
    7 Debbie PA
    8 Julie BA
    9
    10 W/C 30/05 06/06 13/06
    11 PM
    12 BA
    13 PA


    The top table will be populated with peoples availability, a blank cell will represent when an employee is available. So in the lower table I am looking to populate cells B1113 with a formula that will calculate the number of resources available for that week, for the corresponding job type. I am hoping that the formula can look across the whole table so can be easily reproduced for the next week in the lower table, and will react if an employers job role changes in the upper table. I have previously created similar formulas to what I require with SUMIFS functions, but the difference to these is I now need to COUNTIF, and this function will not let me COUNTIF based on criteria outside the range/cells that are actually being counted.


    So broken down, the formula that I would like in cell B11 would ideally look something like this- COUNTIFS(C3:L8,"=""",$B3:$B8,"="&A11,C$2:G$2,AND(>=B10,<B10+5))


    I understand that the above formula will no work for a number of reason, but I hope that the idea of it gives a clearer idea of what I am trying to achieve. I am looking to return the number of cells that are blank, that correspond to the week commencing 30/05, and that have the job role of a PM.


    Any help would be greatly appreciated.


    Andrew









  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,595
    Articles
    0
    Excel Version
    365
    Already answered to AndrewPhoenix's satisfaction elsewhere.
    It is only courteous to say so everywhere you have posted this topic.

    Cross posted without links:
    http://www.mrexcel.com/forum/excel-q...ide-range.html
    AndrewPhoenix, for your information, you should always provide links to your cross posts. It's a rule here and at many other sites.
    If you have cross posted at other places, please add links to them too.
    Why? Have a read of http://www.excelguru.ca/content.php?184

  3. #3
    Apologies, I was unaware of the need to post the link, I will be sure to do so in the future. Thanks.

Tags for this Thread

Posting Permissions

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