I think the best way to explain what I want to do is to show a similar formula that I'm 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 team's 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 we'd have to sum up the goals from both the N and O columns to come up with an average.
Another thing, I don't want to use the player's team in the formula (columns G & H). The reason for this is if a player changes teams I'm 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 recuiterMember since Mar 14, 2020 Piyush Chetan D
from Bahia, Brazil