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

Question

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.

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?

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, Jun 26 '20 at 12:25

JvdV · Accepted Answer · 2020-06-26T15:57:11.987

3

I think you are after something like this:

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)

edited Jun 26 '20 at 15:57

answered Jun 26 '20 at 13:24

JvdV

41,931
5
24
46

1

Nice use of XML filter for summing individual cells with a LF replacement! I am noting down this trick. – jamheadart Jun 26 '20 at 13:28
1

It's very useful @jamheadart, you might find [this](https://stackoverflow.com/q/61837696/9758194) interesting in that case =) – JvdV Jun 26 '20 at 13:32
@JvdV How would I get it to work across the two sheets? – Vaishal Patel Jun 26 '20 at 15:17
@VaishalPatel, you'd have to use the right references. This was just an example. Note, you'd need Excel 2013 or higher for `FILTERXML` to be available. – JvdV Jun 26 '20 at 15:32
Thought as much, have 2010 unfortunately – Vaishal Patel Jun 26 '20 at 15:42
@VaishalPatel, see my update. I added another option in! – JvdV Jun 26 '20 at 15:57
Thanks will test soon :) – Vaishal Patel Jun 26 '20 at 20:33
@VaishalPatel. You might want to open a new question and explain the situation? – JvdV Jul 04 '20 at 18:15
The answer works for the situation in the question. If you retrieve zero then you have extended the data and might need to extend ranges in the actual formula. However, right now I cannot tell for sure since I won't get zero. – JvdV Jul 07 '20 at 07:38
I deduced it to the way to I have the column d, works when separated by a space. But, won't when separated by a line break – Vaishal Patel Jul 08 '20 at 07:02

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

1 Answers1