One (short but complex) option is:
=SUM(IF(TRANSPOSE(A2:A100)=C2:C5,D2:D5))

Hope it helped
Ola Sandström


Note:
This is an Array formula (=it does several calculation in one cell)
All array formulas must be confirmed by holding down Ctrl and Shift then hit
Enter.
Just hit - the normal - Enter will not work -- #VALUE!

C2:D5 is the reference list:
Text Rate
Excellent 1
Good 2
Fair 3
Poor 4