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

1. ## Count blanks, in a range defined by criteria outside range

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

It is only courteous to say so everywhere you have posted this topic.

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.
Why? Have a read of http://www.excelguru.ca/content.php?184

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