Count if /Count ifs help

[Imran Azam]

Hi Guys

I have the below data , i am trying to do a formula that would count the number of "open" status the Manager "roy" has.

i tried a countifs but could get it right

can any one provide any help?

[TABLE="width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]name[/TD]
[TD]manager[/TD]
[TD]id[/TD]
[TD]status[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]james[/TD]
[TD]roy[/TD]
[TD]1[/TD]
[TD]open[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]jack[/TD]
[TD]roy[/TD]
[TD]1[/TD]
[TD]closed[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]alex[/TD]
[TD]Ian[/TD]
[TD]1[/TD]
[TD]open[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]jack[/TD]
[TD]roy[/TD]
[TD]1[/TD]
[TD]open[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]jones[/TD]
[TD]Ian[/TD]
[TD]1[/TD]
[TD]open[/TD]
[/TR]
</tbody>[/TABLE]


Thank you in advance

 

[AlanY]

Book1
	A	B	C	D
1	name	manager	id	status
2	james	roy	1	open
3	jack	roy	1	closed
4	alex	Ian	1	open
5	jack	roy	1	open
6	jones	Ian	1	open
7
8
9	roy	open	2
Sheet4

Cell Formulas
Range	Formula
C9	=COUNTIFS(B2:B6,A9,D2:D6,B9)

 

[Imran Azam]

	A	B	C	D
name	manager	id	status
james	roy	open
jack	roy	closed
alex	Ian	open
jack	roy	open
jones	Ian	open
roy	open

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C9[/TH]
[TD="align: left"]=COUNTIFS(B2:B6,A9,D2:D6,B9)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

thank you

 

[AlanY]

you're welcome