3 Replies Latest reply on Nov 12, 2018 9:43 AM by William Leeden

    Calculation Assistance

    William Leeden

      Hello!

       

      I need assistance in creating a calculation that evaluates a reporting structure. I can't post the workbook, but below is a spreadsheet example:

       

      Formula Help.jpg

      The first requirement is to show the number of direct reports each manager has which is easy enough. The second requirement is to show the number of reports in a group. In the example above, manager A in the first row has 5 reports in his group. This was calculated by the following:

       

      That manager is an associate (associate A in column A)

      That manager has 2 associates (manager A in column B)

      The Excel formula used to calculate column D is =SUMIF(A:A,"A",C:C)+SUMIF(B:B,"A",C:C)

      It's adding the number of direct reports (column C) for each mention of manager A in both column A and B (2+1+2=5)

       

      This gives us an idea of how many associates are related to a group. Hopefully this makes sense. Any assistance is much appreciated.

       

      Thanks!