Count and Match 2 two columns with Lists from other sheets
 

I am looking for a formula that counts how many times a name from a column
list occurs and match it to another column that also has a list from another
worksheet in my workbook and return it to the worksheet with the formula.
e.g.
Column A Column B (from other worksheet)
(Recruited List) (Recruiter List)
Captain Gena
VP John
Captain John
President Gena

My desired result is how many times John (Column B) recruited a Captain
(Column A) and how many times John recruited a VP. The result should show how
many Captains and how many VP's did John recruit as 1 instance each. The same
instances would occur for Gena... In my formula worksheeet I will have the
recruiter name and a column for Captain, VP, President. It would show John
recruited 1 Captain and 1 VP, Gena recruited 1 Captain and 1 President.

Thanks, Miss Kitty!

Count and Match 2 two columns with Lists from other sheets
 

=SUMPRODUCT(--($A$2:$A$200="Captain"),--($B$":$B$200="John"))

--
__________________________________
HTH

Bob

"Miss Kitty" <Miss wrote in message
...
I am looking for a formula that counts how many times a name from a column
list occurs and match it to another column that also has a list from
another
worksheet in my workbook and return it to the worksheet with the formula.
e.g.
Column A Column B (from other worksheet)
(Recruited List) (Recruiter List)
Captain Gena
VP John
Captain John
President Gena

My desired result is how many times John (Column B) recruited a Captain
(Column A) and how many times John recruited a VP. The result should show
how
many Captains and how many VP's did John recruit as 1 instance each. The
same
instances would occur for Gena... In my formula worksheeet I will have the
recruiter name and a column for Captain, VP, President. It would show John
recruited 1 Captain and 1 VP, Gena recruited 1 Captain and 1 President.

Thanks, Miss Kitty!

Count and Match 2 two columns with Lists from other sheets
 

You are genious! Bob
With few modifications it works great!
I entered my worksheet in front of the formula you gave me and chnaged $B$"
to reflect the row I required.
Thanks lots!
--
Miss Kitty


"Bob Phillips" wrote:

=SUMPRODUCT(--($A$2:$A$200="Captain"),--($B$":$B$200="John"))

--
__________________________________
HTH

Bob

"Miss Kitty" <Miss wrote in message
...
I am looking for a formula that counts how many times a name from a column
list occurs and match it to another column that also has a list from
another
worksheet in my workbook and return it to the worksheet with the formula.
e.g.
Column A Column B (from other worksheet)
(Recruited List) (Recruiter List)
Captain Gena
VP John
Captain John
President Gena

My desired result is how many times John (Column B) recruited a Captain
(Column A) and how many times John recruited a VP. The result should show
how
many Captains and how many VP's did John recruit as 1 instance each. The
same
instances would occur for Gena... In my formula worksheeet I will have the
recruiter name and a column for Captain, VP, President. It would show John
recruited 1 Captain and 1 VP, Gena recruited 1 Captain and 1 President.

Thanks, Miss Kitty!