Count if /Count ifs help

Imran Azam

Board Regular
Joined
Mar 15, 2011
Messages
78
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?

ABCD
1namemanageridstatus
2jamesroy1open
3jackroy1closed
4alexIan1open
5jackroy1open
6jonesIan1open

<tbody>
</tbody>


Thank you in advance
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,192
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">name</td><td style=";">manager</td><td style=";">id</td><td style=";">status</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">james</td><td style=";">roy</td><td style="text-align: right;;">1</td><td style=";">open</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">jack</td><td style=";">roy</td><td style="text-align: right;;">1</td><td style=";">closed</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">alex</td><td style=";">Ian</td><td style="text-align: right;;">1</td><td style=";">open</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">jack</td><td style=";">roy</td><td style="text-align: right;;">1</td><td style=";">open</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">jones</td><td style=";">Ian</td><td style="text-align: right;;">1</td><td style=";">open</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">roy</td><td style=";">open</td><td style="text-align: right;background-color: #E2EFDA;;">2</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C9</th><td style="text-align:left">=COUNTIFS(<font color="Blue">B2:B6,A9,D2:D6,B9</font>)</td></tr></tbody></table></td></tr></table><br />
 

Imran Azam

Board Regular
Joined
Mar 15, 2011
Messages
78
ABCD
1namemanageridstatus
2jamesroy1open
3jackroy1closed
4alexIan1open
5jackroy1open
6jonesIan1open
7
8
9royopen2

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet4

Worksheet Formulas
CellFormula
C9=COUNTIFS(B2:B6,A9,D2:D6,B9)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
thank you
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,192
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
you're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,109,518
Messages
5,529,311
Members
409,862
Latest member
lbisacca
Top