Formula for % of employee trained based on certain criteria

[rudolphrc]

Hello web-world,

I have an excel spreadsheet with Mutiple tabs with the same information other than employee name and position. How to calculate training if it is inly for a certain group of employees example blank % of training module complete for only those required to take it (Hourly employee). Original data set has X if completed training only there is nothing defining those that are not required to take the training other than me typing not required.

Employee Name	Position	Training Module 1	Training Module 2	Training Module 3	Training Module 4
Employee 1	upper management	not required	completed	completed
Employee 2	upper management	not required		completed
Employee 3	upper management	not required		completed
Employee 4	mid management	not required		completed
Employee 5	mid management	not required	completed
Employee 6	mid management	not required	completed
Employee 7	hourly employee	completed	completed
Employee 8	hourly employee	completed	completed
Employee 9	hourly employee	completed		completed	completed
Employee 10	hourly employee	completed			completed
Employee 11	hourly employee	completed

 

[Armstrong_N14]

how about?

Book1
	A	B	C	D	E	F	G
1	Employee Name	Position	Training Module 1	Training Module 2	Training Module 3	Training Module 4	Completion %
2	Employee 1	upper management	not required	completed	completed		75.00%
3	Employee 2	upper management	not required		completed		50.00%
4	Employee 3	upper management	not required		completed		50.00%
5	Employee 4	mid management	not required		completed		50.00%
6	Employee 5	mid management	not required	completed			50.00%
7	Employee 6	mid management	not required	completed			50.00%
8	Employee 7	hourly employee	completed	completed			50.00%
9	Employee 8	hourly employee	completed	completed			50.00%
10	Employee 9	hourly employee	completed		completed	completed	75.00%
11	Employee 10	hourly employee	completed			completed	50.00%
12	Employee 11	hourly employee	completed				25.00%
Sheet1

Cell Formulas
Range	Formula
G2:G12	G2	=(COUNTIF(C2:F2,"not required")+COUNTIF(C2:F2,"completed"))/COUNTA($C$1:$F$1)

 

[rudolphrc]

I think that is a good formula if you wanted to track each individual employee. What would it be if you wanted to track % of those that completed each training module?

 

[Armstrong_N14]

maybe this?

Book10
	A	B	C	D	E	F	G	H	I	J
1	Employee Name	Position	Training Module 1	Training Module 2	Training Module 3	Training Module 4	Completion %		Module	% Completion
2	Employee 1	upper management	not required	completed	completed		75%		Training Module 1	100.00%
3	Employee 2	upper management	not required		completed		50%		Training Module 2	45.45%
4	Employee 3	upper management	not required		completed		50%		Training Module 3	45.45%
5	Employee 4	mid management	not required		completed		50%		Training Module 4	18.18%
6	Employee 5	mid management	not required	completed			50%
7	Employee 6	mid management	not required	completed			50%
8	Employee 7	hourly employee	completed	completed			50%
9	Employee 8	hourly employee	completed	completed			50%
10	Employee 9	hourly employee	completed		completed	completed	75%
11	Employee 10	hourly employee	completed			completed	50%
12	Employee 11	hourly employee	completed				25%
Sheet1

Cell Formulas
Range	Formula
I2:I5	I2	=TOCOL(C1:F1)
J2	J2	=(COUNTIF(C$2:C$12,"not required")+COUNTIF(C$2:C$12,"completed"))/COUNTA($A$2:$A$12)
J3	J3	=(COUNTIF(D$2:D$12,"not required")+COUNTIF(D$2:D$12,"completed"))/COUNTA($A$2:$A$12)
J4	J4	=(COUNTIF(E$2:E$12,"not required")+COUNTIF(E$2:E$12,"completed"))/COUNTA($A$2:$A$12)
J5	J5	=(COUNTIF(F$2:F$12,"not required")+COUNTIF(F$2:F$12,"completed"))/COUNTA($A$2:$A$12)
G2:G12	G2	=(COUNTIF(C2:F2,"not required")+COUNTIF(C2:F2,"completed"))/COUNTA($C$1:$F$1)
Dynamic array formulas.

 

[rudolphrc]

maybe this?

Book10
	A	B	C	D	E	F	G	H	I	J
1	Employee Name	Position	Training Module 1	Training Module 2	Training Module 3	Training Module 4	Completion %		Module	% Completion
2	Employee 1	upper management	not required	completed	completed		75%		Training Module 1	100.00%
3	Employee 2	upper management	not required		completed		50%		Training Module 2	45.45%
4	Employee 3	upper management	not required		completed		50%		Training Module 3	45.45%
5	Employee 4	mid management	not required		completed		50%		Training Module 4	18.18%
6	Employee 5	mid management	not required	completed			50%
7	Employee 6	mid management	not required	completed			50%
8	Employee 7	hourly employee	completed	completed			50%
9	Employee 8	hourly employee	completed	completed			50%
10	Employee 9	hourly employee	completed		completed	completed	75%
11	Employee 10	hourly employee	completed			completed	50%
12	Employee 11	hourly employee	completed				25%
Sheet1

Cell Formulas
Range	Formula
I2:I5	I2	=TOCOL(C1:F1)
J2	J2	=(COUNTIF(C$2:C$12,"not required")+COUNTIF(C$2:C$12,"completed"))/COUNTA($A$2:$A$12)
J3	J3	=(COUNTIF(D$2:D$12,"not required")+COUNTIF(D$2:D$12,"completed"))/COUNTA($A$2:$A$12)
J4	J4	=(COUNTIF(E$2:E$12,"not required")+COUNTIF(E$2:E$12,"completed"))/COUNTA($A$2:$A$12)
J5	J5	=(COUNTIF(F$2:F$12,"not required")+COUNTIF(F$2:F$12,"completed"))/COUNTA($A$2:$A$12)
G2:G12	G2	=(COUNTIF(C2:F2,"not required")+COUNTIF(C2:F2,"completed"))/COUNTA($C$1:$F$1)
Dynamic array formulas.

maybe this?

Book10
	A	B	C	D	E	F	G	H	I	J
1	Employee Name	Position	Training Module 1	Training Module 2	Training Module 3	Training Module 4	Completion %		Module	% Completion
2	Employee 1	upper management	not required	completed	completed		75%		Training Module 1	100.00%
3	Employee 2	upper management	not required		completed		50%		Training Module 2	45.45%
4	Employee 3	upper management	not required		completed		50%		Training Module 3	45.45%
5	Employee 4	mid management	not required		completed		50%		Training Module 4	18.18%
6	Employee 5	mid management	not required	completed			50%
7	Employee 6	mid management	not required	completed			50%
8	Employee 7	hourly employee	completed	completed			50%
9	Employee 8	hourly employee	completed	completed			50%
10	Employee 9	hourly employee	completed		completed	completed	75%
11	Employee 10	hourly employee	completed			completed	50%
12	Employee 11	hourly employee	completed				25%
Sheet1

Cell Formulas
Range	Formula
I2:I5	I2	=TOCOL(C1:F1)
J2	J2	=(COUNTIF(C$2:C$12,"not required")+COUNTIF(C$2:C$12,"completed"))/COUNTA($A$2:$A$12)
J3	J3	=(COUNTIF(D$2:D$12,"not required")+COUNTIF(D$2:D$12,"completed"))/COUNTA($A$2:$A$12)
J4	J4	=(COUNTIF(E$2:E$12,"not required")+COUNTIF(E$2:E$12,"completed"))/COUNTA($A$2:$A$12)
J5	J5	=(COUNTIF(F$2:F$12,"not required")+COUNTIF(F$2:F$12,"completed"))/COUNTA($A$2:$A$12)
G2:G12	G2	=(COUNTIF(C2:F2,"not required")+COUNTIF(C2:F2,"completed"))/COUNTA($C$1:$F$1)
Dynamic array formulas.

and if you wanted to track % of each work force category upper management Mid management, hourly worker?

 

[Armstrong_N14]

COUNTIFS function - Microsoft Support

How to use the COUNTIFS function in Excel to apply criteria to cells, across multiple ranges, and count the number of times all criteria are met.

support.microsoft.com