# Return A Count Based On Two Columns

#### mcgonma

##### Board Regular
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).

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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

M.

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

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?

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.

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

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

Replies
4
Views
75
Replies
1
Views
223
Replies
8
Views
231
Replies
0
Views
193
Replies
9
Views
336

1,207,011
Messages
6,076,143
Members
446,187
Latest member
LMill

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back