excel formula

Question

Hi,

I have a detailed excel table.

In this table, there writes the details of all the soccer sport teams in a given country.

One columns shows the name of the team.

One column shows the name of the played match.

One columns shows the number of the goals that team scored in that game.

The rest of the columns show the name of the players depending on their position in that game.

In this regard, if a player plays for example as a goald keper in that match, his name appears in the first columns. Buf if the same player plays as the defender in another match, his name will appear in the 2. Or 3. Columns ect.

What I want to achieve is to see in how many matches a player took part in the team and the number of goals scored in these   games, regardless of his position.

I want to be able to sort the players

1-     according to the number  of matches they played regardless of their positions

2-     according to the number of goals scored in the games they played regardless of their positions.

I need a formula to be able to do that.

Please note that I have around 800 lines in this table. What kind of formula should I be using?

Answer 1

The formulas are the easy part. As an example, assume columns A through C contain the game details and columns D through M contain the names of the players in who played in that match (11 players, one for each position). If there are substitutions, you may need more columns but this is just an example.

The number of games a player played in is given by the formula

COUNTIF(D1:M800, "=players_name")

The number of goals scored in games a player played in is given by the formula

SUMIF(D1:M800,"=players_name",C1:C800)

The hard part is associating each of these numbers with the player the apply to. For that I think you will need a macro One approach for the macro is:

• Define a range named Players that includes all the cells with names (e.g., $D$1:$D$800).
• Define a range named Goals that includes all the cells with goals.
• Open a new worksheet in the workbook.
• Collect all the names in the original worksheet into column A.
• Sort the names and remove duplicates.
• In cell B1 enter the formula =COUNTIF(Players, D1).
• In cell C1 enter the formula =SUMIF(Players , D1, Goals).
• Propogate the contents of cells B1 and C1 down to the last player name.

You can now sort the new page based on either column to get what you described.