SumIfs based on Lookup table

[Ross191]

I have two tables (examples below). I'm trying to calculate a "final score" by assesing what values were selected. I have ten total options in my real excel file that I can't share due to confidentiality constraints.

table 1 (inputs)

Options Selected (note the options selected below are within one cell seperated by a line break)	"Final Score" (aka the expected output (see table 2 for the scoring weights) - I'm trying to determine what the formula should be in the cells below
[Option 1] [Option 2] [Option 3]	6
[Option 1] [Option 3]	5

table 2 (lookup table)

Option	Value (weight)
Option 1	3
Option 2	1
Option 3	2

 

[jasonb75]

Assuming no duplicate options in any single cell

=SUM(IF(ISNUMBER(SEARCH(options list, selected options)),values list))

Must be array confirmed with ctrl shift enter if you're using excel 2019 or older.

 

[Ross191]

I can't seem to get that to work. Here is my formula based on your formula:

=SUM(IF(ISNUMBER(SEARCH(Z2:Z12,J2)),AA2:AA12))

Z2:Z12 = options list
J2 = equals one cell with 3 options within the cell
AA2:AA12 = option list values

I've also made sure it's an array

Assuming no duplicate options in any single cell

=SUM(IF(ISNUMBER(SEARCH(options list, selected options)),values list))

Must be array confirmed with ctrl shift enter if you're using excel 2019 or older

 

[jasonb75]

Your formula looks correct, what exactly is it (or isn't it) doing?

Have you tried using the evaluation tool on the formula?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Ross191]

Your formula looks correct, what exactly is it (or isn't it) doing?

Have you tried using the evaluation tool on the formula?

So sorry. That worked. I had test data in the sheet that seemed to be throwing it off, so I removed the old test data and added new. Your formula then worked! Thanks so much.

-M