Problem:

Range A2:G5 contains the scores of four games.
Each row includes the names and scores of the three players who took part in the game specified in column A.
We want to calculate the total number of points scored in all four games by each of the players listed in cells A8:A12.

Solution:

Use the SUMIF function as shown in the following formula:
=SUMIF($B$2:$F$5,A8,$C$2:$G$5)