Excel sports template rank table

Question

I am currently working on a sports template prediction for the upcoming Euro 2020. I haven't gotten that far yet but below is an example of a group. So the "Tecken" column says which team won the game, if there is a 1 there the home team won, if there is a X there it was a draw and if it's a 2 there the away team won. What I would like to do is have a formula in the points column in the second table that checks which is the home team playing and if there is a 1 under the "tecken" column add 3 points to the points table. And if there is a X I would like to add 1 points for that team. I also need to do this for the away teams and add points (if there is a 2 in the "tecken" column) to the points table. Sorry for the bad explanation, is that clearer?

So for spain it would check the first game and see that spain is not playing. It would check the second game and see that spain is the home team and that there is a 1 in the "tecken" column and add 3 points to the points table and so on.

(GF is goals scored by each country and GA is goals scored against each country and GD is the goal difference)

Answer 1

You can use a formula with CountIFs. Conceptually:

• Count how many times the Home team is Spain AND the Tecken is 1 and multiply by three
• Count how many times the Home team is Spain AND the Tecken is X
• Count how many times the Away team is Spain AND the Tecken is 2 and multiply by three
• add these values together.

Something like this. Adjust the ranges to your sheet.

=(Countifs(C:C,K2,I:I,1)*3)+Countifs(C:C,K2,I:I,"X")+(Countifs(E:E,K2,I:I,2)*3)