How to join mimic many to many behavior in Google Sheets?

Question

I have 2 tables in a Google Sheets file. And I need to join the tables to make a calculation:

Example:

In this example, I need to get the total score or sum of all reports a team is associated with.

I know I can get all the reports IDs associated with the team by using this:

FILTER(F2:F, E2:E = A2)

but I don't know what to do from there.

I'm thinking about finding all report scores whose id is in that list. and then using a Sum like this:

SUMIF(I2:I, (INCLUDE(H2, FILTER(F2:F, E2:E = A2))))

Where INCLUDE determines if a value is inside a set of elements or range.

Example with expected results: example

Answer 1

paste in C2 and drag down:

=ARRAYFORMULA(IFERROR(SUM(VLOOKUP(FILTER(G:G, F:F=A2), I:J, 2, 0))))