working with multiline cells without spliting them in Excel

0
votes

I have a table with hundreds of rows in a very heavy excel workbook, I give a simplified like so:

enter image description here

And another table with combined text:

enter image description here

Is there a way I could have a formula that would allow me to check in the 1st table the individual values of each multiline cell and then add them in the total, to obtain something like this:

enter image description here

The point here being that the file might change in the future, rows could be added or deleted and so on, so I would like to be able to obtain the totals without splitting the multiline cells.

I've tried countIf but I don't think that's the proper way to go. Any help would be appreciated.

1
excelexcel-formula

1 Answers

2
votes

Multiple options really. You could try:

enter image description here

Formula in B10:

=SUMPRODUCT(ISNUMBER(FIND(A$2:A$7,A10))*B$2:B$7)

Or, a bit more in line of checking each individual value inside your combination:

=SUMPRODUCT(VLOOKUP(FILTERXML("<t><s>"&SUBSTITUTE(A10,CHAR(10),"</s><s>")&"</s></t>","//s"),A$2:B$7,2,0))