Name search formula within text then sumifs from sheet2 to sheet1

@Pepe Le Mokko

to reply to your post question: Will the full name always be in last and second last position?


Shruder Reply: No, it will be a variable potentially each cell.

If you do not want to use Text to Columns are you OK with a helper column?

In the attached column D 'Sheet2' this normalizes the names. It someone may be able to shorten it.

Formula:

=SUBSTITUTE(TRIM(MID(SUBSTITUTE(REPLACE(B1,FIND("-",B1),99,"")&"/"&REPLACE(B1,FIND("-",B1),99,""),",",REPT(" ",100)),100,100)),"/","")

then in D2 'Sheet1'

Formula:

=SUMPRODUCT(--ISNUMBER(SEARCH(Sheet2!$D$1:$D$100,C2)),Sheet2!$C$1:$C$100)

Edit Here is a shorter helper formula column D 'Sheet2'.

Formula:

=REPLACE(REPLACE(B1,1,FIND(",",B1)+1,""),FIND("-",REPLACE(B1,1,FIND(",",B1)+1,"")),99,LEFT(B1,FIND(",",B1)-1))

Please try D2

=SUMIFS(Sheet2!$C:$C,Sheet2!$B:$B,TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",20)),20))&", "&TRIM(LEFT(RIGHT(SUBSTITUTE(C2," ",REPT(" ",20)),40),20))&"*")