Scoring cells in a column of text

[Cory]

I have a single column of cells (let's say A) with a large number of rows.
Each cell contain various strings of text each about the length of a
sentence. I would like to create a second column that would grade the
quality of the text in cell A based on how many keywords are contained in
the text. There would be several keywords and each would have a numerical
value.

So an example would be that A3 would be "My neighbor Fred doesn't like my
dog anymore" and cell A4 would be "Janet is in love with my dog". Then I
would like to grade the words "Fred" as 2, "Janet" as 1 and "dog" as 3. The
result would be that B3 would equal 5 and B4 would be 4. I can now sort
them, and the other thousand, by how well they scored. Any ideas?

What I tried: [You can ignore this bit if you want] At first I envisioned
some formula in B3 that would refer to the second worksheet where keywords
were listed next to their values. However I couldn't find any functions to
suit that so I created a third and more columns whose first two rows, C1 &
C2 for example, contained the keyword and value. Then I used the SEARCH
function to return the value from C2 if A3 contained C1. Then I reckoned I'd
create several columns like this and sum them across back to B3 for the
score. That's when I hit a wall. If the cell in column A doesn't contain any
of the keywords the result is "#Value!". Now if I try to sum across it
fails. Arggg...

So maybe you can tell me how to avoid the "#Value!" problem or maybe you can
tell me of some other function I was unaware of. I'm guessing the last since
I am not familiar with most Excel functions and you all problem do.

Thanks in advance.

 

[Ken Wright]

One way trying it how you were doing:-

=IF(ISERROR(SEARCH("Fred",A1)),0,2)+IF(ISERROR(SEARCH("Janet",A1)),0,1)+IF(ISERROR(SEARCH("Dog",A1
)),0,3)

 

[Cory]

ISERROR? Kewl! Never heard of it. Sounds like what I need. Thanks, you're
always a great help! And fast too!