count if cell has text in two columns

[Mitch123]

Hi everyone,
I'm new to this so please excuse my inexperience.
Just wondering how to count the number of cells that contains certain text in one column but only if there is text in an adjacent column. Here is an example,
<table class="MsoTableGrid" style="border-collapse:collapse;border:none;mso-border-alt:solid windowtext .5pt; mso-yfti-tbllook:1184;mso-padding-alt:0cm 5.4pt 0cm 5.4pt" border="1" cellpadding="0" cellspacing="0"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes"> <td style="width:64.5pt;border:solid windowtext 1.0pt; mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="86"> Column 1
</td> <td style="width:64.5pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="86"> Column 2
</td> </tr> <tr style="mso-yfti-irow:1"> <td style="width:64.5pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="86"> ABC
</td> <td style="width:64.5pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="86"> Bob
</td> </tr> <tr style="mso-yfti-irow:2"> <td style="width:64.5pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="86">
</td> <td style="width:64.5pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="86"> Bob
</td> </tr> <tr style="mso-yfti-irow:3"> <td style="width:64.5pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="86">
</td> <td style="width:64.5pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="86"> Bob
</td> </tr> <tr style="mso-yfti-irow:4"> <td style="width:64.5pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="86"> ACB
</td> <td style="width:64.5pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="86"> Jill
</td> </tr> <tr style="mso-yfti-irow:5"> <td style="width:64.5pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="86"> CAB
</td> <td style="width:64.5pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="86"> John
</td> </tr> <tr style="mso-yfti-irow:6"> <td style="width:64.5pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="86"> ABC
</td> <td style="width:64.5pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="86"> John
</td> </tr> <tr style="mso-yfti-irow:7;mso-yfti-lastrow:yes"> <td style="width:64.5pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="86"> ACB

</td> <td style="width:64.5pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="86"> John
</td> </tr> </tbody> </table>
I want to be able to count the number of cells in Column 2 that contains either "Bob" or "John" but only if there is text in Column 1. The answer should be 4 (actual text in Column1 shouldn't matter so long as it contains text).

Can anyone help?

Mitch

 

[anandvarma]

try this

Excel Workbook
	A	B	C	D
1	ABC	Bob	Count of Bob	1
2		Bob	Count of John	3
3		Bob
4	ACB	Jill
5	CAB	John
6	ABC	John
7	ACB	John
Sheet3

 

[MrRajKumar]

For prior XL2007 versions;

=SUMPRODUCT(--(A2:A8<>""),(B2:B8="Bob")+(B2:B8="John"))

For XL2007 or later;

=SUM(COUNTIFS(A:A,"<>",B:B,{"Bob","John"}))

 

[Mitch123]

Thanks heaps guys, it worked.