Formula help!

[PimontheMoon]

Hello all,

I am new to the forum and although not new to Excel, I am far from advanced! I am hoping someone will be able to point me in the right direction of a formula that I can use please.

I have people data which has payroll number, job title and Directorate columns for every employee in the organisation. I need a formula (or to use helper columns) to work out how many distinct roles have more than 1 employee in the role, split by Directorate. 

I have managed to get the total number of distinct roles using SUMPRODUCT, but I’m struggling on how to add in another criteria to get this info split by Directorate. 

Any help would be very much appreciated. Thanks in advance :smiling_face_with_smiling_eyes:

[#2]

@PimontheMoon Perhaps you can use a Power Pivot table.

Directorate in the Column field,

Job Title in the Row field and

Job Title in the Value field (set to distinct Count).

Demonstrated in the attached file.

[#3]

Thank you! That does give the distinct roles but doesn’t seem to filter only on roles that have more than one employee?

[#4]

@PimontheMoon 

=SUMPRODUCT(N(COUNTIFS($A$2:$A$25,E15,$B$2:$B$25,$E$2:$E$12)>1))

An alternative could be this formula with the data layout of this example: