Sum if cells contain a range of multiple comma separated values (From another cell)

Question

I have a list of attacks together with powers. I want to get the total sum of all powers which matches multiple comma separated values in another cell.

This is how my first sheet looks like:

Sheet 1

| Attack               | Power         |
|:---------------------|:--------------|
| nuke                 | 300           |
| nuke and laser       | 500           |
| megalasernuke        | 1000          |
| fist                 | 10            |
| weak fist and bow    | 50            |
| supreme fist and bow | 100           |

Sheet 2

Using =SUMIF('Sheet1'!A:A;"*"&A2&"*";'Sheet1'!B:B) in column B, my second sheet can return the sum of all power containing one value:

| Attack               | Sum of Power  |
|:---------------------|:--------------|
| nuke                 | 1800          |
| fist                 | 160           |

I want to be able to sum if the cells in Sheet1 contains multiple comma separated values (all of them), resulting in the following:

| Attack               | Sum of Power  |
|:---------------------|:--------------|
| nuke,laser           | 1500          |
| fist,bow             | 150           |
| fist,bow,nuke        | 0             |
| fist                 | 160           |

Is this possible to do without custom excel scripts?

Answer 1

it is not pretty but I can't figure another method, as you want to iterate twice:

=SUMPRODUCT(ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE(D2,",",REPT(" ",99)),1,99)),$A$2:$A$7))*ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE(D2,",",REPT(" ",99)),99,99)),$A$2:$A$7))*ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE(D2,",",REPT(" ",99)),198,99)),$A$2:$A$7))*$B$2:$B$7)

This only works up to three comma separated values.