Calculating a qualitative score in Excel

[la_coyote]

Hi All,

I wanted to calculate the total score for each Car (to eventually find if it meets a tolerance level) based on the individual qualitative evaluation given in each category. How do I add the individual numbers that each letter represents across the whole row/ The table is as follows and I want to calculate the "Sum" column for each row:

Control Self Assessment.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		Category 1	Category 2	Category 3	Category 4	Category 5	Category 6	Category 7	Category 8	Category 9	Category 10	Sum					Short Legend	Long Legend	Tolerance	Score
2	Car 1	N	P	L	F	P	L	P	L	L	L						N	Not Achieved	0-15%	5
3	Car 2	F	P	L	P	L	L	L	L	F	P						P	Partially Achieved	15-50%	10
4	Car 3	P	L	P	L	L	L	L	L	F	P						L	Largely Achieved	50-85%	15
5	Car 4	N	P	L	F	P	L	P	L	L	L						F	Fully Achieved	85-100%	20
6	Car 5	P	L	P	L	L	L	L	L	F	P
7	Car 6	P	L	L	P	L	L	L	P	L	F
8	Car 7	N	P	L	F	P	L	P	L	L	L
9	Car 8	F	P	L	P	L	L	L	L	F	P
10	Car 9	P	L	P	L	L	L	L	L	F	P
11	Car 10	N	P	L	F	P	L	P	L	L	L
12	Car 11	P	L	P	L	L	L	L	L	F	P
13	Car 12	P	L	L	P	L	L	L	P	L	F
14	Car 13	F	P	L	P	L	L	L	L	F	P
15	Car 14	P	L	P	L	L	L	L	L	F	P
Sheet2

 

[Toadstool]

Hi La_Coyote,

Does this work?

La_Coyote.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		Category 1	Category 2	Category 3	Category 4	Category 5	Category 6	Category 7	Category 8	Category 9	Category 10	Sum					ShortLegend	Long Legend	Tolerance	Score
2	Car 1	N	P	L	F	P	L	P	L	L	L	130					N	Not Achieved	0-15%	5
3	Car 2	F	P	L	P	L	L	L	L	F	P	145					P	Partially Achieved	15-50%	10
4	Car 3	P	L	P	L	L	L	L	L	F	P	140					L	Largely Achieved	50-85%	15
5	Car 4	N	P	L	F	P	L	P	L	L	L	130					F	Fully Achieved	85-100%	20
6	Car 5	P	L	P	L	L	L	L	L	F	P	140
7	Car 6	P	L	L	P	L	L	L	P	L	F	125
8	Car 7	N	P	L	F	P	L	P	L	L	L	130
9	Car 8	F	P	L	P	L	L	L	L	F	P	145
10	Car 9	P	L	P	L	L	L	L	L	F	P	140
11	Car 10	N	P	L	F	P	L	P	L	L	L	130
12	Car 11	P	L	P	L	L	L	L	L	F	P	140
13	Car 12	P	L	L	P	L	L	L	P	L	F	125
14	Car 13	F	P	L	P	L	L	L	L	F	P	145
15	Car 14	P	L	P	L	L	L	L	L	F	P	140
Sheet1

Cell Formulas
Range	Formula
L2:L15	L2	=(COUNTIF($B2:$K2,$Q$2)*$T$2)+(COUNTIF($B2:$K2,$Q$3)*$T$3)+(COUNTIF($B2:$K2,$Q$4)*$T$4)+(COUNTIF($B2:$K2,$Q$5)*$T$5)

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Calculating a numeric score from a qualitative score - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[la_coyote]

Hi La_Coyote,

Does this work?

La_Coyote.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		Category 1	Category 2	Category 3	Category 4	Category 5	Category 6	Category 7	Category 8	Category 9	Category 10	Sum					ShortLegend	Long Legend	Tolerance	Score
2	Car 1	N	P	L	F	P	L	P	L	L	L	130					N	Not Achieved	0-15%	5
3	Car 2	F	P	L	P	L	L	L	L	F	P	145					P	Partially Achieved	15-50%	10
4	Car 3	P	L	P	L	L	L	L	L	F	P	140					L	Largely Achieved	50-85%	15
5	Car 4	N	P	L	F	P	L	P	L	L	L	130					F	Fully Achieved	85-100%	20
6	Car 5	P	L	P	L	L	L	L	L	F	P	140
7	Car 6	P	L	L	P	L	L	L	P	L	F	125
8	Car 7	N	P	L	F	P	L	P	L	L	L	130
9	Car 8	F	P	L	P	L	L	L	L	F	P	145
10	Car 9	P	L	P	L	L	L	L	L	F	P	140
11	Car 10	N	P	L	F	P	L	P	L	L	L	130
12	Car 11	P	L	P	L	L	L	L	L	F	P	140
13	Car 12	P	L	L	P	L	L	L	P	L	F	125
14	Car 13	F	P	L	P	L	L	L	L	F	P	145
15	Car 14	P	L	P	L	L	L	L	L	F	P	140
Sheet1

Cell Formulas
Range	Formula
L2:L15	L2	=(COUNTIF($B2:$K2,$Q$2)*$T$2)+(COUNTIF($B2:$K2,$Q$3)*$T$3)+(COUNTIF($B2:$K2,$Q$4)*$T$4)+(COUNTIF($B2:$K2,$Q$5)*$T$5)

hi Toadstool,

Thank you so much, you rock! i can sleep peacefully tonight!

Best wishes,
La_Coyote

 

[Toadstool]

hi Toadstool,

Thank you so much, you rock! i can sleep peacefully tonight!

Best wishes,
La_Coyote

You're welcome!
Thanks for the feedback and happy dreams!

 

[la_coyote]

Hi Fluff,

Apologies, hadnt known about that rule. But thank you for pointing out. I haven't cross-posted anywhere else

Will not happen again.

Regards,

La_Coyote