Count blanks, in a range defined by criteria outside range

AndrewPhoenix

New member
Joined
May 16, 2016
Messages
2
Reaction score
0
Points
0
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-


ABCDEFGHIJKL
1Week 22Week 23
2NameJob role30/0531/0501/0602/0603/0606/0607/0608/0609/0610/06
3JohnPMHolHolHol
4StevePM
5StuartBA
6AndrewPMOff siteOff siteOff site
7DebbiePA
8JulieBA
9
10W/C30/0506/0613/06
11PM
12BA
13PA


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 B11:D13 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








 
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-...nks-range-defined-criteria-outside-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
 
Apologies, I was unaware of the need to post the link, I will be sure to do so in the future. Thanks.
 
Back
Top