You can hard code it if you want

=SUMPRODUCT(COUNTIF(A2:A30,{"Superior";"Pretty Good";"Fair";"Pretty
Bad";"Poor"}),{3;2;0;-2;-3})


will sum the values for each descriptive word in A2:A30


if you want to use your hidden sheet


=SUMPRODUCT(COUNTIF(A2:A30,Sheet2!$A$1:$A$5),Sheet 2!$B$1:$B$5)


where the descriptions are in A1:A5 and their values in B1:B5




--


Regards,


Peo Sjoblom



I'm sorry, I should have been more clear. Suppose I have a Sheet1
with a column of five descriptive values:

Superior
Fair
Pretty bad
Pretty good
Superior

In another hidden sheet (Sheet2), I have a master of all possible
descriptive values and their associated score in two adjacent columns:

Superior 3
Pretty good 2
Fair 0
Pretty bad -2
Poor -3

What I'd like to do is add a total score to the bottom of the column
in Sheet1, which looks up the scores associated with these descriptive
values using the data in Sheet2. At this point, I'm not even
concerned about having separate totals for positive and negative
values. I'd settle for one score that simply combines all the points.

Thanks again.