Name search formula within text then sumifs from sheet2 to sheet1

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))&"*")