Using SUMPRODUCT to sum duplicate scores with same criteria

I have two summary sheets that pull scores (points) from several other events sheet's cells. If the team name of the reference row matches the summary cell's team name (row), the points are put in the corresponding place in the summary. The problem I have is that there are usually more than one score for the same team name (2 separate squads from same team). When this is the case, only one of the scores is picked up by the summary. I need the summary cells to sum the quantities of the event sheets with matching criteria. Below is the function that works (except for summing the duplicate scores).

For Teams:
=INDEX(Event_1!$B$2:$B$18,MATCH($A3,Event_1!$A$2:$A$18,0),0)+INDEX(Event_2!$B$2:$B$18,MATCH($A3,Event_2!$A$2:$A$18,0),0)

* I’m pretty sure I need a SUMPRODUCT in here but I’m not sure how to make it work.

EVENT_1 & EVENT_2 are the sheets where A is the team column, B is the score column, and C is the column that lists both team players (comma delimited).
A is the team name column in the TEAMS summary sheet – B is the cumulative score (point totals)
A is the player name column in the PLAYERS summary sheet – B is the cumulative score (point totals)

I need to summarize the individual player scores just like the team scores. I tried to use text to columns to put the names from C into columns D & E. But when I try to use the array D2:E18 in the MATCH function, it did not work.

I’m attaching my sample workbook to make it easier to understand. Thanks very much for the help!

You get the #N/A because of the 2 column range in the MATCH() function

Change your formula to

=INDEX(Event_1!$B$2:$B$18,MATCH($A2,Event_1!$D$2:$D$18,0),0)

Is that what you want?

Thank you, but no, that only returns column D. It needs to search and return both columns D & E.

Is this what you want
=SUM(SUMIF(Event_1!D2:D18,A2,Event_1!B2:B18),SUMIF(Event_1!E2:E18,A2,Event_1!B2:B18))

I was going to offer another alternative - maybe

=SUMIF(Event_1!C2:C18,"*"&A2&"*",Event_1!B2:B18)

Yes, it does work fine with the provided samples. I checked for Bill - Billy, Jon - Jonathan types before using it and didn't see any. I should have added the caveat, though.

I always try to avoid SUMPRODUCT - got wrist slapped many moons ago for using it unnecessarily and have never forgotten. It may even have been by you!

Excellent! Thank you both very much for your assistance! Using the SUMPRODUCT you suggested works great for the players. That saves me a few steps of seperating them into different columns.

As a follow-up question, how could I rank these from the highest to the lowest scores?