We help IT Professionals succeed at work.
Get Started

Countif or SumProduct Help needed for Headcount Report

402 Views
Last Modified: 2012-05-09
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
CERTIFIED EXPERT
Most Valuable Expert 2013
Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE