Football Data - Analysing recent team form (I have half of the solution in SUMPRODUCT)

AlphaQuail

New Member
Joined
Sep 14, 2013
Messages
14
https://drive.google.com/file/d/0B0csbmxO50sARnlNd203a1MyMzg/edit?usp=sharing

Above is a link to the Google Drive doc for this post

I'm trying to sum together goals scored for and against for a particular team in the previous X number of games played. The doc shows home teams in column F and away in column G. The formula I have already can only sum the previous X number of games goals (6 in my working example) for home appearances and away appearances separately. I need to tweak the formula I have to account both column F & G together.

In this example, I'm essentially looking to determine how goals AFC Wimbledon have scored over the past 6 times they have appeared (it would be 6, 4 at home and 2 away) and the number of goals score against them (it would be 7 here, 3 home and 4 away) I have highlighted these for clarity.

I've included the formulas I've been working with to date which is a combination of another post response and help from a colleague. I know this isn't far off the end product with my attempts to nest a FIND function somewhere in this looking at a CONCAT of F&G not delivering a sound result

Can any one help?

Thanks

Lee
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

ukmikeb

Well-known Member
Joined
Jul 10, 2009
Messages
2,757
Hi

Try for the For goals :-
Code:
=SUMPRODUCT(--($A$11:$A$16="AFC Wimbledon")+--($B$11:$B$16="AFC Wimbledon"),--(INDEX(($D$11:$D$16,$E$11:$E$16),,,IF($G$11:$G$16="H",1,2))))
and for the Against goals :-
Code:
=SUMPRODUCT(--($A$11:$A$16="AFC Wimbledon")+--($B$11:$B$16="AFC Wimbledon"),--(INDEX(($D$11:$D$16,$E$11:$E$16),,,IF($G$11:$G$16="A",1,2))))
both entered with Control-Shift-Enter and change the ranges to suit.

ps Sorry I couldn't get the true ranges because I couldn't download the file.

hth
 

ukmikeb

Well-known Member
Joined
Jul 10, 2009
Messages
2,757
Hi

Assuming the Team names are in F and G then the above becomes -
Goals For :-
Code:
=SUMPRODUCT(--($F$11:$F$16="AFC Wimbledon")+--($G$11:$G$16="AFC Wimbledon"),--(INDEX(($I$11:$I$16,$J$11:$J$16),,,IF($L$11:$L$16="H",1,2))))
Goals Against :-
Code:
=SUMPRODUCT(--($F$11:$F$16="AFC Wimbledon")+--($G$11:$G$16="AFC Wimbledon"),--(INDEX(($I$11:$I$16,$J$11:$J$16),,,IF($L$11:$L$16="A",1,2))))
Both entered with Control-Shift-Enter.

hth
 
Last edited:

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Why not:

=SUMIF(F8:F13,"AFC Wimbledon",J8:J13)+SUMIF(G8:G13,"AFC Wimbledon",K8:K13)

=SUMIF(F8:F13,"AFC Wimbledon",K8:K13)+SUMIF(G8:G13,"AFC Wimbledon",J8:J13)
 

AlphaQuail

New Member
Joined
Sep 14, 2013
Messages
14
Hi Mike,

Thanks for coming back to me on this one. You'll have to forgive my simple-mindedness here. The above don't seem to give the desired result however. I think the formulas provided use the results column (for home or away) in determining whether Wimbledon were at home or not whereas that colulm is the actual result of the game (home win, away win, draw)

If you can get into the file I've attached a snapshot of the file in the hopes this might help? Essentially I need to count how many goals have been scored by wimbledon over their last 6 appearances and how many goals they've had scored against them. I'll then apply the same logic to the away team in the game I'm analysing (which in this example is Accrington.

Again, many thanks in advance.



Lee
 

AlphaQuail

New Member
Joined
Sep 14, 2013
Messages
14
hi Andrew,

I didn't see you original post until just now. I've entered those in and it seems to deliver with one slight snag. i only want to sum the last 6/most recent 6 times Wimbledon appear, which is fine for a fixed data range, but I want to be able to copy this formula down. Therefore in a longer data set, I won't be summing the goals scored beyword 6 appearances ago.

Thanks

lee
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Does each row in your lists in columns F:G always contain the team you want to analyse?
 

AlphaQuail

New Member
Joined
Sep 14, 2013
Messages
14
If it helps I had one solution from a previous thread which for home team goals for was:

=IF(COUNTIF(F$3:F13,$F13)>=$K$1,SUMPRODUCT((ROW(J$3:J13)>=LARGE((F$3:F13=$F13)*ROW(J$3:J13),$K$1))*(F$3:F13=$F13)*J$3:J13),"n/a")

This summed the most recent goals as desired but treat home appearances and away appearances separately. I need this to be combined

Thanks

Lee
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Is this what you want?

=IF((COUNTIF(F$3:F13,$F13)+COUNTIF(G$3:G13,$F13))>=$K$1,SUMPRODUCT((ROW(J$3:J13)>=LARGE(((F$3:F13=$F13)+(G$3:G13=$F13))*ROW(J$3:J13),$K$1))*(F$3:F13=$F13)*J$3:J13),"n/a")
 

Forum statistics

Threads
1,089,467
Messages
5,408,430
Members
403,204
Latest member
pth

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top