Name search formula within text then sumifs from sheet2 to sheet1

Hello,

I am looking to use a search formula for first, last name then sumif between two tabs

Sheet1: Search for below name in column text

Sheet1.PNG


Sheet2: Search same name, and if there is a match then sum the totals per that condition

Sheet2.PNG

So Essentially, if the search can match the names exact, then sum the totals in a new column on Sheet 1.


I have been trying the IF search then sumifs it agrees to sheet 2 search results. I do not have a name list that I can use so not sure how the search will work.

I really just want to find a match each time those columns from both sheets find a name match and then sumif the totals per that match from sheet to to a new column in Sheet1.

I do not want to use text to columns at this point.

Any thoughts on formula solutions?

=IF(AND(OR(ISNUMBER(SEARCH("Bill Nye",$c2)),$f2="Bill Nye",SUMIFS from Sheet 2,0))))