Count blanks, in a range defined by criteria outside range

[AndrewPhoenix]

Hello

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

<colgroup><col style="width: 37px"><col width="101"><col width="58"><col width="58"><col width="58"><col width="58"><col width="58"><col width="58"><col width="58"><col width="58"><col width="58"><col width="58"><col width="58"></colgroup><tbody style="margin: 0px; padding: 0px; border: 0px;">
</tbody>

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,"<wbr>="&A11,C$2:G$2,AND(>=B10,<B10+<wbr>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

 

[Marcelo Branco]

Hi Andrew

Welcome to Mr Excel forum

See if this formula does what you need

B11
=SUMPRODUCT(($C$2:$L$2>=B$10)*($C$2:$L$2<=B$10+4)*($B$3:$B$8=$A11)*($C$3:$L$8=""))
copy across and down

Hope this helps

M.

 

[oldbrewer]

				W21					W22			W23
		PM	2	4	1	3	5	8	11	3	6	9
		BA	2	4	2	4	6	8	10	2	4	6
		PA	1	2	1	2	3	4	5	1	2	3
		PM	2	2	1	2	2	3	3	3	3	3
		BA	2	2	2	2	2	2	2	2	2	2
		PA	1	1	1	1	1	1	1	1	1	1
	Name	Job role	W21	W21	W22	W22	W22	W22	W22	W23	W23	W23
	John	PM	Hol	Hol	Hol	1	1	1	1	1	1	1
	Steve	PM	1	1	1	1	1	1	1	1	1	1
	Stuart	BA	1	1	1	1	1	1	1	1	1	1
	Andrew	PM	1	1	Off site	Off site	Off site	1	1	1	1	1
	Debbie	PA	1	1	1	1	1	1	1	1	1	1
	Julie	BA	1	1	1	1	1	1	1	1	1	1
	col B											col M
				I used 1 to mean available
				the helper rows are all automated
				the table below pulls the data using offset match
				if this way wd be acceptable I will post the formulas
	Week	W21	W22	W23
	PM	4	11	9			formula giving PM W21 = 4
	BA	4	10	6
	PA	2	5	3			=OFFSET($C$1,MATCH($B37,$C$2:$C$4,0),MATCH(C$36,$D$1:$M$1,0))

<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[AndrewPhoenix]

Marcelo! Thank you, that has worked perfectly!! And thanks for the quick response too!

Andrew

 

[AndrewPhoenix]

Thanks for the response oldbrewer, Marcelo's response above has worked though, thanks for the help anyway

 

[Marcelo Branco]

Marcelo! Thank you, that has worked perfectly!! And thanks for the quick response too!

Andrew

You are welcome. Glad to help

M.