Trying to extend a SUMPRODUCT Formula

[mcgonma]

I have a worksheet with 30 business units in ColumnA and 65 roles in Row1.
This is supposed to refer to another worksheet that has all 1100 employees listed and assigned to the roles that are in Row1.
I want the sheet that I’m working on to calculate the number of employees in each business unit are assigned to of the 65 roles.
I will gladly provide the file to someone if it will help you see the context of my issue.
Thanks!

[NBVC]

=COUNTIFS(Sheet2!$A:$A,$A2,Sheet2!$B:$B,B$1)

copied down and across

where Sheet2 is name of the Employee list sheet.

Column A in that sheet contains the Business Units, Column B contains the Roles. In the active sheet A2 list first Business Unit and B1 list first Role.

If it doesn't work or you can't match with your data, then post a sample file.

[mcgonma]

Thank you for providing this formula. I tried it in E4 of "Roles By Agency Count" sheet in the attached. Unfortunately it did not work.
I think the big challenge is that in what you're calling "Sheet2" (Stakeholder Roster in the attached file), the same role is not assigned in the same column for all employees.
SAP Project Stakeholder Roster Role Mapping - MASTER - 20140521 - TEST2.xlsx

QUOTE=NBVC;13047]=COUNTIFS(Sheet2!$A:$A,$A2,Sheet2!$B:$B,B$1)

copied down and across

where Sheet2 is name of the Employee list sheet.

Column A in that sheet contains the Business Units, Column B contains the Roles. In the active sheet A2 list first Business Unit and B1 list first Role.

If it doesn't work or you can't match with your data, then post a sample file.[/QUOTE]

[NBVC]

Ok, so you are counting multiple columns for the second condition. Well, we can't use COUNTIFS then....

And you can't use SUMPRODUCT because many of your results in the Stakeholder Roster & Role Map sheet have #N/A errors (hidden).

So either you fix those using IFERROR() to convert errors to formula blanks, or you use COUNT(IF()) array formula:

=COUNT(IF(('Stakeholder Roster & Role Map'!$E$15:E$222=$B4)*('Stakeholder Roster & Role Map'!$L$15:$AY$222=E$2),1))

confirmed with CTRL+SHIFT+ENTER not just ENTER, then copy down and across.

If you get rid of the #N/A errors in the sheet, then your SUMPRODUCT would be:

=SUMPRODUCT(('Stakeholder Roster & Role Map'!$E$15:E$222=$B4)*('Stakeholder Roster & Role Map'!$L$15:$AY$222=E$2))