count number of employees holding the same position in that department

[Raehan]

I am trying to count number of employees holding the same position in their department. What formula should I be using countifs or any other? I would like to count them in column I in the attached spreadsheet. I've also provided the sample how it should calculate. For it should count number of employees based on column H and E.

Thanks,
Rae

[cubangt]

Try this in cell I2

Please Login or Register  to view this content.

then copy down.. see if that helps.

[Pete_UK]

If you were to put the department codes Bio, CS and DO in cells L1 to N1 and a unique list of job titles from K2 downwards, then you could have this formula in L2:

=COUNTIFS($H:$H,$K2,$E:$E,L$1)

and you can copy this across and down to give you a summary table of the count of job titles against Department.

Hope this helps.

Pete