How can you in Excel total predefined values depending on cell values

1
votes

I have a spreadsheet with two tabs. The first one contains Vehicle Types and a numeric score value.

Second Tab has like a variety of these vehicle types and what should be the total score. Depending on the vehicle types present in the respective neighbour cell.

See images below for illustration.

Tab/sheet 1Tab/Sheet 2

Is there a way via formula to get the total, in Column B in sheet 2, of the corresponding numeric values of column a from sheet 1?

For example, as per the illustration B2 in sheet would total 3; whereby in sheet 1 bus has a score of 1 and car 2.

Update:

As per the answer below, I have used the formula;

=SUMPRODUCT(ISNUMBER(FIND(" "&sheet1!A$2:A$4&" "," "&SUBSTITUTE(A4,CHAR(10)," ")&" "))*sheet1!B$2:B$4)

However, I am unfortunately getting zero as the value. Changing the line breaks in column A in sheet2 I am duly able to get the total. Is there a way to do it so irrespective of how the list is presented in the column the total will work?

1
excelexcel-formula
I'm not quite sure to understand, but =SUM.IF(Sheet1!A:A;A2;Sheet1!B:B) should do the trick. (when A2 on sheet2 and any field in A column in sheet A match perfectly) – mansuetus

1 Answers

3
votes

I think you are after something like this:

enter image description here

Formula in E2:

=SUMPRODUCT(VLOOKUP(FILTERXML("<t><s>"&SUBSTITUTE(D2,CHAR(10),"</s><s>")&"</s></t>","//s"),A$2:B$4,2,FALSE))

If one has O365 you could just use SUM instead since it would auto-CSE the formula.

If you don't have Excel 2013 or later, you could try the following as another option (shorter but not my favourite):

=SUMPRODUCT(ISNUMBER(FIND(" "&A$2:A$4&" "," "&SUBSTITUTE(D2,CHAR(10)," ")&" "))*B$2:B$4)