We help IT Professionals succeed at work.

Countif or SumProduct Help needed for Headcount Report

Could someone please resolve a problem for me.  My thinking is to use Countif or Sumproduct, but it doesn't have to be (short of macros).  I'm an advanced user of excel, but I don't know some of the more advance formula that could simplify this problem.

Column (E) is the Top 99 Managers in our company.  
Column (F) was calculated to return the number of employees in the organization of the managers in Column (E).

Column (A) is each employee.  Mgr 1 is the CEO, his code is A, so everyone reports to him and there are 1413 employees.  The CEO has 5 direct reports (AB - Finance, AC - Legal, AD - Operations, AE - ILS, AG - HR).  The umbrella goes down from there.  Individual contributor codes end with "A" and Manager codes do not end with "A"

Column (B) indicates in the employees in Column (A) are a manager
Column (C) indicates in the ethnicity of the employees in Column (A)
Column (D) indicates in the gender of the employees in Column (A)

Using our CEO (Mgr 1) as an example I need to know how many of employees (EmpCode) in his org are Manager and Non Manager.
Out of the manager population I need to know how many are female and how many or non minority (not WH)

Example
The VP of HR (AG) has  (see A1399:A1415)
16 Employees (Cell F96)
4 Managers (indicated by “Yes”)
0 Minority Managers (EmpCode Cell A1402, A1406, A1408 and A1413 all have Ethnicity of WH)
1 Female Manager (D1402)

Employee-Cnt.xls
Comment
Watch Question

Most Valuable Expert 2013
Commented:
Hello ablove3,
You can use SUMPRODUCT for these, e.g. in G2 copied down
=SUMPRODUCT((LEFT(A$2:A$1415,LEN(E2))=E2)*(B$2:B$1415="yes")*(A$2:A$1415<>E2))
and then to expand for H2 copied down
=SUMPRODUCT((LEFT(A$2:A$1415,LEN(E2))=E2)*(B$2:B$1415="yes")*(A$2:A$1415<>E2)*(C$2:C$1415<>"WH"))
see attached for others
Note: that in Excel 2007 you could use COUNTIFS to do the same....
regards, barry

26248714.xls
Audra BreedloveSr HRIS Analyst

Author

Commented:
Barry, I seriously have a tear in my eye.  I know, "I need to get a life".  I've had some trumatic knew today regarding the help of my manager and I just couldn't focus, but I knew that I needed to get this done.  I spent so many hours trying to figure this out and just got nowhere.

You made this so easy and once again, I get to learn more from you.  I appreciate you Barryhoudini.
Audra BreedloveSr HRIS Analyst

Author

Commented:
Barryhoudini is the best.  I know that my questions seem quick and easy to you, but it makes such a difference when you respond and so quickly.  

Thank you Barry, I was hoping that you'd respond.  I can always trust that you'll be quick and give me the exact answer that I need.,