Results 1 to 4 of 4

Thread: Trying to extend a SUMPRODUCT Formula

  1. #1

    Trying to extend a SUMPRODUCT Formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,436
    Articles
    0
    Excel Version
    Excel 2016
    =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.


  3. #3

    Attaching a file

    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]

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,436
    Articles
    0
    Excel Version
    Excel 2016
    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))


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •