# Solved: Sum values associated with keywords inside cell

1. ## Solved: Sum values associated with keywords inside cell

I have a spreadsheet in which I need to do something unusual.

The setup:
Each cell will contain a list of keywords. Each keyword is worth a specific value (1-4). A cell may contain any number of keywords in any order, however each keyword only repeats once.

Data sheet
 source value Bob 1 Mary 3 Hill 2 Brown 4

Results sheet
 input value list value sum Phil,Bob,Brown

Part1 :Getting a list that transposes the words in the cell to a list of associated values.

In the results sheet I expect the value list to return 1, 2, 4

I've done this 2 ways but I feel both are ugly and nonuser friendly, would be great if someone had a better/simpler way to do this.

These work but are ugly:
Formula:
`Please Login or Register  to view this content.`

Formula:
`Please Login or Register  to view this content.`

Part2 : Sum up the associated values for every word in the cell.

[S]I've looked at sumifs and sumproduct but I couldn't get this to work! [/S]

[Formula]
=SUMPRODUCT(--ISNUMBER(SEARCH(Data!A2:A5,A2)),Data!B2:B5)[formula]

3. ## Re: Sum values associated with keywords inside cell

I think the upload is being blocked by done kind of security. The info advice should be enough for a potential answer though.

4. ## Re: Sum values associated with keywords inside cell

5. Originally Posted by Gregb11
The solution is :

Formula:
`Please Login or Register  to view this content.`

There are currently 1 users browsing this thread. (0 members and 1 guests)