Ok, so sheet2 is essentially just a data tab, like a small database table would look. This could be made a lot easier by adding one more column into your data, say column J, with a sequential number starting at 1 with the header "ID".
Then you can rely on this ID field in the final lookup as follows:
A2 down: Names - you fill manually, correct? Let's say A2 contains "John Jones" for the formulae below.
B2: =SUMIFS(Sheet2!C:C,Sheet2!A:A,H2,Sheet2!B:B,A2) - This is the score John achieved in Maths on the date displayed in H2.
C2: =SUMIFS(Sheet2!D:D,Sheet2!A:A,H2,Sheet2!B:B,A2) - This is the score John achieved in Physics on the date displayed in H2.
D2: =SUMIFS(Sheet2!E:E,Sheet2!A:A,H2,Sheet2!B:B,A2)
E2: =SUMIFS(Sheet2!F:F,Sheet2!A:A,H2,Sheet2!B:B,A2)
F2: =SUMIFS(Sheet2!G:G,Sheet2!A:A,H2,Sheet2!B:B,A2)
G2: =INDEX(Sheet2!I:I,MATCH(SUMIFS(Sheet2!J:J,Sheet2!A:A,H2,Sheet2!B:B,A2),Sheet2!J:J,0)) - Because you can't use aggregate functions like SUM() on text ranges, this uses the sumif to find the ID instead, and looks up off that.
H2: =MAX(Sheet2!A:A)
Please note, this is fully untested, as it seemed a lot of effort to recreate your table!
but hopefully this will work for you.
Cheers