Return A Count Based On Two Columns

[mcgonma]

In sheet 1, Column A has a list of 670 people's Names, Column B has their Division and then Column C has their Job Title (there are 25 titles that are in a list on another worksheet).
On sheet 2 Column A3:A55 has the list of Divisions and C1:V1 has all of the Job Titles.
On sheet 2, I want to do a count of how many people listed in Sheet 1 in a particular division have a particular Job Title.
For Example, how many of the 670 people are in the Comptroller's and have the Job Title "Hiring Manager" (which is A* in the Roles worksheet).

 

[Marcelo Branco]

See if this example helps

Sheet1

A B C (headers in row 1)

Names	Division	Job
Name1	Div1	Job1
Name2	Div2	Job2
Name3	Div3	Job1
Name4	Div4	Job2
Name5	Div1	Job3
Name6	Div1	Job3
Name7	Div4	Job1
Name8	Div2	Job2
Name9	Div3	Job1
Name10	Div5	Job2

<tbody>
</tbody>

Sheet2

A B C D

	Job1	Job2	Job3
Div1	1	0	2
Div2	0	2	0
Div3	2	0	0
Div4	1	1	0
Div5	0	1	0

<tbody>
</tbody>

Formula in B2
=SUMPRODUCT(--(Shee1!$B$2:$B$11=$A2),--(Shee1!$C$2:$C$11=B$1))

copy across and down

Adjust to suit

M.

 

[Robert Mika]

In Excel 07/10 you may consider:
=COUNTIFS(Sheet1!$B$2:$B$11,$A2,Sheet1!$C$2:$C$11,B$1)

 

[mcgonma]

Hi Robert. I'm using Excel 2003. Is there somewhere that I could send you a sample file to look at and see what you can come up with?

 

[Marcelo Branco]

oops...

There are typos in my formula

=SUMPRODUCT(--(Shee1!$B$2:$B$11=$A2),--(Shee1!$C$2:$C$11=B$1))

try

=SUMPRODUCT(--(Sheet1!$B$2:$B$11=$A2),--(Sheet1!$C$2:$C$11=B$1))

M.

 

[mcgonma]

I adjusted for the typos but still could not get it to work. I sent you an email with the sample file.

 

[Marcelo Branco]

I adjusted for the typos but still could not get it to work. I sent you an email with the sample file.

Ok, see my reply