Need help making a complicated sumif formula in Excel

I think the best way to explain what I want to do is to show a similar formula that Im using now.

=COUNTIFS(INDIRECT(AF&ROW($AF8)&:AF&MATCH(*&$G7&Sheet3!$C$11+$AD7&*,$AB:$AB,0)),*&AG7&*,INDIRECT(AB&ROW($AB8)&:AB&MATCH(*&$G7&Sheet3!$C$11+$AD7&*,$AB:$AB,0)),*&$G7&*)

This formula counts the number of times a selected player has appeared in his teams starting lineup in the last x games.

Sheet3!$C$11 = x games
AF = this is the column with the lineup data for both home and away teams
G7 = the team of the selected player
AD7 = counts how many times the selected team appears in the rows above
AB = the formula in this column is (AB7 for example) is G7&AD7&H7&AE7
AG7 = the selected player

What I want to do now is find the average number of goals scored by the team of a selected player in the last x games when he was in the starting lineup.

Column N = home team goals
Column O = away team goals

This is trickier because if we look at the last 10 games for example, this will be a mixture of home and away games, so wed have to sum up the goals from both the N and O columns to come up with an average.

Another thing, I dont want to use the players team in the formula (columns G & H). The reason for this is if a player changes teams Im going to get an error (no results) when looking at his last x games with his new team.

This can probably be done by looking for games the player started in column AF and matching those with the numbers in N and O.
First 11 names are players from the home team and the rest are from the away team. If it would make things easier I can make separate columns for the home and away lineups.
Thanks
About the recuiter
Member since Mar 14, 2020
Vaishakh Vinod
from Sao Paulo, Brazil

---

---

Offer to work on this project closes in 1 days!
Are you interested in this Opportunity?

Looking for help? Checkout our video tutorial
How to search and apply for jobs

How to apply? Do you have more questions about the Job?
See frequently asked questions