How to calculate number of employees per department position

what is the formula to calculate how many employees are filled in the positions per department. See the attached spreadsheet.

you could just add to your sumproduct....
=SUMPRODUCT(('Position Control'!$B:$B=B$2)*('Position Control'!$C:$C=$A8)*('Position Control'!A:A<>""))
a word of advice though, try not to use full column references with sumproduct, it will slow things down

A better option would be this...
=COUNTIFS('Position Control'!$A:$A,"<>""",'Position Control'!$B:$B,'P#s'!B$2,'Position Control'!$C:$C,'P#s'!$A3)

thanks buddy. this formula works better. but how do we eliminate the zero if no value. is there any additional we can add
=SUMPRODUCT(('Position Control'!$B:$B=B$2)*('Position Control'!$C:$C=$A8)*('Position Control'!A:A<>""))

If you mean you want the cell to be "blank" instead of showing 0, then maybe....
=if(COUNTIFS('Position Control'!$A:$A,"<>""",'Position Control'!$B:$B,'P#s'!B$2,'Position Control'!$C:$C,'P#s'!$A3)=0,,"",COUNTIFS('Position Control'!$A:$A,"<>""",'Position Control'!$B:$B,'P#s'!B$2,'Position Control'!$C:$C,'P#s'!$A3))

Again, pleease note that using full column ranges in sumproduct will slow your file down. (this is not the case with countifs though)

Like this...

=IFERROR(1/(1/COUNTIFS('Position Control'!$B:$B,B$2,'Position Control'!$C:$C,$A8,'Position Control'!A:A,"<>")),"")

Sorry, I had an extra , in there

Buddy. this formula work for to identify how many job title total per department. but I need to know how many employees are filled in for each job title per department. see the chart for more information. I added the current formula into the yellow columns.
=if(COUNTIFS('Position Control'!$A:$A,"<>""",'Position Control'!$B:$B,'P#s'!B$2,'Position Control'!$C:$C,'P#s'!$A3)=0,"",COUNTIFS('Position Control'!$A:$A,"<>""",'Position Control'!$B:$B,'P#s'!B$2,'Position Control'!$C:$C,'P#s'!$A3))

E.g.: 4B has 3 NURSE CLINICIAN I job title but only 1 employee filled. so the result would be 1.
another way is 4B has total 19 job title but only 11 employees filled.
so the formula should identify how many employees filled in each job title per department.

You can use the more efficient COUNTIFS function.

Here's the sample file that uses the COUNTIFS function and removes all the zeros...

Sample excel ans (1).xlsx

In the column S formulas, replace the plus signs with commas.

From this:

=SUM(B3+D3+F3+H3+J3+L3+N3+P3)

To this:

=SUM(B3,D3,F3,H3,J3,L3,N3,P3)

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.