So, in the case where A1 does not contain a space, eg: "Simon", what would you want the answer to be? Just the instances where there is an exact match?

If so, change the last element to this:

=IF(NOT(ISERR(FIND(" ",A1))),SUMPRODUCT((Sheet1!B1:B10)*--(LEFT(Sheet1!A1:A10,FIND(" ",A1)-1)=LEFT(A1,FIND(" ",A1)-1))*(((LEN(Sheet1!A1:A10)=FIND(" ",A1)-1)+(MID(Sheet1!A1:A10,FIND(" ",A1),1)=" ")))),SUMPRODUCT((Sheet1!B1:B10)*--(Sheet1!A1:A10=A1)))

Or, when a1="Simon ", would you want to capture "Simon" and "Simon ", but not "Simon Lloyd", then you could do this:

=IF(NOT(ISERR(FIND(" ",TRIM(A1)))),SUMPRODUCT((Sheet1!B1:B10)*--(LEFT(Sheet1!A1:A10,FIND(" ",A1)-1)=LEFT(A1,FIND(" ",A1)-1))*(((LEN(Sheet1!A1:A10)=FIND(" ",A1)-1)+(MID(Sheet1!A1:A10,FIND(" ",A1),1)=" ")))),SUMPRODUCT((Sheet1!B1:B10)*--(TRIM(Sheet1!A1:A10)=TRIM(A1))))

does that get it? Not sure what you want the answer to be if just "Simon" was in A1.