# Counting based on other criteria - COUNTIFS maybe?

#### mattyn

##### Board Regular
Good Morning All

I have inherited a huge spreadsheet of one worksheet (called ManningPlot) detailing the manpower situation across a number of departments across the business. This spreadsheet has one row per person and details a lot of info regarding that person and his/her position. There are in excess of 2000 rows (hence over 2000 manpower positions).

Col A details the department name that the person works in. A total of 66 different departments. Dept1, Dept2, Dept 3 etc
Col B details the name of the person. Where there is no incumbent in that position, it is blank.

For info, and stuff I do not need to manipulate yet, - Col C to I detail more information pertaining to that person (gender, employee number etc). Col J to L are hidden and not needed yet. Col M and N detail information to the position the incumbent fills (M is position name, N is position number).

Col P details the effect of any medical limitations that person maybe suffering with and is scaled 0 (No effect), 1 (minimal effect), 2 (Moderate effect) and 3 (severe effect).

Col Q details whether the person is away (possibly on leave, course etc) and is detailed Yes or No.

Col R details the effect of that position not being filled and is scaled 0 (No effect), 1 (minimal effect), 2 (Moderate effect) and 3 (severe effect).

Col S details the extraneous duty that person is charged with.

What I want to do:

On a separate worksheet (Sheet 2), I want an abridged version I can present the main data along the lines of one row per dept as follows:

Col A to detail the Dept Name - done.
Col B to detail the number of positions allocated to that dept. (a count of Col A on the previous sheet). I have used =countif('manningplot'!A2:A2500,"Dept1") -seems to work!

Col C to detail the number of persons allocated to that dept. I want to ignore where positions are not filled. (A count of Col B on the previous sheet). So a count of names in ManningPlot Col B where ManningPlot Col A = Dept1.

Col D to detail the number of people away on courses within that dept.( A count of ManningPlot Col Q = Yes where ManningPlot ColA = Dept1).

Col E,F & G to detail the number of medical limitation effects ( i.e. number of minimal, moderate and severe) per department.
Col H, I & J to detail the number of gapping effects ( i.e. number of minimal, moderate and severe) per department.

Hope this makes sense - I see the function Countifs and Sumifs but does seem I am getting confused. Please help.

#### tyija1995

##### Well-known Member
Hey,

SUMPRODUCT should work pretty well for this type of problem.

Assuming you are using the second row in Sheet2 - then try:

C2:
Code:
``SUMPRODUCT((manningplot!\$A\$2:\$A\$2500="Dept1")*(manningplot!\$B\$2:\$B\$2500<>""))``
D2:
Code:
``SUMPRODUCT((manningplot!\$Q\$2:\$Q\$2500="Yes")*(manningplot!\$A\$2:\$A\$2500="Dept1"))``
E2 (drag across to G2)
Code:
``SUMPRODUCT((manningplot!\$P\$2:\$P\$2500=COLUMN()-4)*(manningplot!\$A\$2:\$A\$2500="Dept1"))``
H2 (Drag across to J2)
Code:
``SUMPRODUCT((manningplot!\$R\$2:\$R\$2500=COLUMN()-7)*(manningplot!\$A\$2:\$A\$2500="Dept1"))``
Instead of using "Dept1" you could use cell A2 in Sheet2 to make it more dynamic as well.

EDIT: Added absolute references incase formulas are dragged down Sheet2.

Last edited:

#### mattyn

##### Board Regular
Got it - awesome thank you.

1,082,373
Messages
5,365,070
Members
400,821
Latest member
kezza123

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
• Complex Heat Map using conditional formatting
Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
• Conditional formatting
Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...