Formula help needed

[TMILJVIPM]

Hello All!
I need help with a formula.
In cell L2 im needing it to calculate words in the cell range E2:k2 and turn the words into a number but also total the sum of each value. For example if cell E2 has "Excellent" then its value is 4, and if cell F2 has "Above" then its value is 3, and the if cells G2, H2, I2, J2, K2 all have "Average" then each cell would get a value of 2. So in sum cell L2 would show the total value score of 17.

excellent = 4
Above = 3
Average = 2
Below = 1
Unacceptable = 0

Hopefully this makes sense and I explained it well. Thanks for any help in advance!

 

[JamesCanale]

MrExcelPlayground6.xlsx
	E	F	G	H	I	J	K	L
1
2	Excellent	Excellent	Average	Below	Above	Unacceptable	Average	16
3
4
5	Excellent	4
6	Above	3
7	Average	2
8	Below	1
9	Unacceptable	0
Sheet24

Cell Formulas
Range	Formula
L2	L2	=SUM(VLOOKUP(E2:K2,E5:F9,2,FALSE))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[TMILJVIPM]

MrExcelPlayground6.xlsx
	E	F	G	H	I	J	K	L
1
2	Excellent	Excellent	Average	Below	Above	Unacceptable	Average	16
3
4
5	Excellent	4
6	Above	3
7	Average	2
8	Below	1
9	Unacceptable	0
Sheet24

Cell Formulas
Range	Formula
L2	L2	=SUM(VLOOKUP(E2:K2,E5:F9,2,FALSE))

Thank you very much....it worked.

 

[TMILJVIPM]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I sure will do this as soon as I finish my project. Thanks for the advice.

 

[Fluff]

I sure will do this as soon as I finish my project.

I would suggest you do it before asking any further questions. That way members will not need to ask.