Counting based on other criteria - COUNTIFS maybe?

[mattyn]

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]

Hey,

SUMPRODUCT should work pretty well for this type of problem.

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

C2:

SUMPRODUCT((manningplot!$A$2:$A$2500="Dept1")*(manningplot!$B$2:$B$2500<>""))

D2:

SUMPRODUCT((manningplot!$Q$2:$Q$2500="Yes")*(manningplot!$A$2:$A$2500="Dept1"))

E2 (drag across to G2)

SUMPRODUCT((manningplot!$P$2:$P$2500=COLUMN()-4)*(manningplot!$A$2:$A$2500="Dept1"))

H2 (Drag across to J2)

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.

 

[mattyn]

Got it - awesome thank you.