# calculate the goal scored and conceded in the last 6 football Matches

1. ## calculate the goal scored and conceded in the last 6 football Matches

Please friends how can I get the formula that will calculate the goal scored and conceded in the last 6 football Matches at home, away, and overall in an excel spreadsheet,I can provide an attachment if needed. I will be very happy to get an absolute answer and help thanks in advance.. from mansh

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #1 requires good titles. "Please Help" doesn't tell us anything about your question. I have updated it for you this time because you are a new member. --6StringJazzer

2. ## Re: calculate the goal scored and conceded in the last 6 football Matches

We can't very easily give you a formula if we don't know anything about how your data is laid out, so it would be a good idea to attach a sample workbook. Follow the instructions in the yellow banner at the top of the screen to do this.

Pete

3. ## Re: calculate the goal scored and conceded in the last 6 football Matches

Pete_UK Thank you for your reply and also to the moderator for helping me out also, I have uploaded an attachment to the workbook

4. ## Re: calculate the goal scored and conceded in the last 6 football Matches

A sample including at LEAST 6 matches for the selected team will help.

5. ## Re: calculate the goal scored and conceded in the last 6 football Matches

I was thinking that if a certain team have not played up to 6 matches then, it should display the played matches, but if they have played up to 6 then, it should show last 6 matches…thanks in advance

6. ## Re: calculate the goal scored and conceded in the last 6 football Matches

I remember doing something like this years ago - I'll look tomorrow and see if I can find the references to it.

Pete

7. ## Re: calculate the goal scored and conceded in the last 6 football Matches

Ok thank you friend, I will patiently wait for your help..

8. ## Re: calculate the goal scored and conceded in the last 6 football Matches

I didn't find the file I was looking for - looked through the file names for 1500+ files that I have submitted here, but didn't find any that seemed suitable (there were many called sample or book1 !!)

However, I did notice when I re-opened your file that it was very similar in terms of headings with the file in this thread:

https://www.excelforum.com/excel-pro...ell-value.html

Did you base your file on that one?

Pete

9. ## Re: calculate the goal scored and conceded in the last 6 football Matches

Thanks for your response, yes the headings are the same. The data was downloaded from the football data site, I was trying to send the site along but were not allowed to do so

10. ## Re: calculate the goal scored and conceded in the last 6 football Matches

please help, if I could only get the formula for getting the last nth matches played, according to my table layout, then I guess I could continue from there.

11. ## Re: calculate the goal scored and conceded in the last 6 football Matches

I think this is the thread I was thinking about (I thought it was years ago, though):

https://www.excelforum.com/excel-for...or-league.html

You might also want to look at this thread:

https://www.excelforum.com/excel-for...ue-arrows.html

Hope this helps.

Pete

12. ## Re: calculate the goal scored and conceded in the last 6 football Matches

Ok, Thank you lemme go through them, I will get back to you soon.

13. ## Re: calculate the goal scored and conceded in the last 6 football Matches

I only see win, draw, and loss from the files you sent me the links. But I mean I wanted the goals scored and conceded from the last nth matches played...

14. ## Re: calculate the goal scored and conceded in the last 6 football Matches

Hello friends, can anybody help me out on this challenge am having, I can't still figure out goals scored and conceded in the last nth matches played.anybody with help yet?

15. ## Re: calculate the goal scored and conceded in the last 6 football Matches

Helper column X

=IF(OR(\$D2=\$Y\$7,\$E2=\$Y\$7),IF(MAX(\$X\$1:X1)>6,"",COUNTIF(\$D\$2:E2,\$Y\$7)),"")

in Z7

=MAX(\$X\$2:\$X\$20)

in AA7

=COUNTIFS(\$X\$2:\$X\$20,"<>",\$D\$2:\$D\$20,\$Y\$7,\$H\$2:\$H\$20,"H")+COUNTIFS(\$X\$2:\$X\$20,"<>",\$E\$2:\$E\$20,\$Y\$7,\$H\$2:\$H\$20,"A")

in AB7

=COUNTIFS(\$X\$2:\$X\$20,"<>",\$D\$2:\$D\$20,\$Y\$7,\$H\$2:\$H\$20,"D")+COUNTIFS(\$X\$2:\$X\$20,"<>",\$E\$2:\$E\$20,\$Y\$7,\$H\$2:\$H\$20,"D")

in AC7

=COUNTIFS(\$X\$2:\$X\$20,"<>",\$D\$2:\$D\$20,\$Y\$7,\$H\$2:\$H\$20,"A")+COUNTIFS(\$X\$2:\$X\$20,"<>",\$E\$2:\$E\$20,\$Y\$7,\$H\$2:\$H\$20,"H")

=COUNTIFS(\$X\$2:\$X\$20,"<>",\$D\$2:\$D\$20,\$Y\$7,\$H\$2:\$H\$20,"A")+COUNTIFS(\$X\$2:\$X\$20,"<>",\$E\$2:\$E\$20,\$Y\$7,\$H\$2:\$H\$20,"H")

in AE7

=SUMIFS(\$G\$2:\$G\$20,\$X\$2:\$X\$20,"<>",\$D\$2:\$D\$20,\$Y\$7)+SUMIFS(\$F\$2:\$F\$20,\$X\$2:\$X\$20,"<>",\$E\$2:\$E\$20,\$Y\$7)

plus Data Validation for TEAM selction (named range "Teams")

For Home Away : amend above formulae

16. ## Re: calculate the goal scored and conceded in the last 6 football Matches

Oh thank you, lemme go through them, i will be back

17. ## Re: calculate the goal scored and conceded in the last 6 football Matches

not working because if i wanted to get their last nth matches played, for example, Liverpool's last match played was on 8/21/21 Liverpool vs Burnley. the question now is how do I get the goals scored and conceded in their recent/last match/matches played for Liverpool?. i await for favorable response. thanks in advance

18. ## Re: calculate the goal scored and conceded in the last 6 football Matches

You need to show expected results:Liverpool beat Norwich 3-0 and Burnley 2-0 so goals scored is 5, conceded 0 (as per results) - what do you expect?.

I am dropping out of this thread.

19. ## Re: calculate the goal scored and conceded in the last 6 football Matches

Sorry if any inconveniences, I want if I say last 1 match played, it will only sum 8/21/21 Liverpool 2 vs Burnley 0 goals , if I say last 2 matches played, it will sum 8/21/21 Liverpool 2 vs and Burnley 0, 8/14/21 Norwich 0 vs Liverpool 3 goals repectively. Thanks

20. ## Re: calculate the goal scored and conceded in the last 6 football Matches

Not what you requested and there is no cell specifing the required number of matches to be considered.

21. ## Re: calculate the goal scored and conceded in the last 6 football Matches

Now you know what am talking about, help me out, I just want nth matches goals calculated dynamically

22. ## Re: calculate the goal scored and conceded in the last 6 football Matches

Last try

HELPER

=IF(OR(\$D2=\$Y\$7,\$E2=\$Y\$7),IF(COUNTIF(\$D\$2:\$E\$20,\$Y\$7)-COUNTIF(\$D\$2:E2,\$Y\$7)+1>\$Z\$2,"",COUNTIF(\$D\$2:\$E\$20,\$Y\$7)-COUNTIF(\$D\$2:E2,\$Y\$7)+1),"")

in AA7

=COUNTIFS(\$X\$2:\$X\$20,"<=" &\$Z\$2,\$D\$2:\$D\$20,\$Y\$7,\$H\$2:\$H\$20,"H")+COUNTIFS(\$X\$2:\$X\$20,"<=" &\$Z\$2,\$E\$2:\$E\$20,\$Y\$7,\$H\$2:\$H\$20,"A")

BOLD changed in other formula

23. ## Re: calculate the goal scored and conceded in the last 6 football Matches

Thank you SO MUCH.. you just nailed it. i am very grateful. I was trying to get my way out on getting the Over/Under goals, BTTS, and putting the teams in a league team respectively, but here am exhausted with logics now, can you still help?

24. ## Re: calculate the goal scored and conceded in the last 6 football Matches

the Over/Under goals, BTTS, and putting the teams in a league team
I haven't a clue what is meant by any of the above.

25. ## Re: calculate the goal scored and conceded in the last 6 football Matches

I have uploaded an attachment to that

26. ## Re: calculate the goal scored and conceded in the last 6 football Matches

No explanation of, [or sample data]

over 2.5 or BTTS

Re the league: you should be able to modify the formulas I gave ( row 7) for "Liverpool" selecting the teams in Z23 down.

AA23

=COUNTIFS(\$D\$2:\$D\$20,\$Z23)+COUNTIFS(\$E\$2:\$E\$20,\$Z23)

AB23

=COUNTIFS(\$D\$2:\$D\$20,\$Z23,\$H\$2:\$H\$20,"H")+COUNTIFS(\$E\$2:\$E\$20,\$Z23,\$H\$2:\$H\$20,"A")

etc

27. ## Re: calculate the goal scored and conceded in the last 6 football Matches

the last formula you sent me just got me lost, I wanted a formula based on recent match not all, now I sent an attachment, in it is a recent form table with all the teams, which I want their Win, Draw, Loss, Goals scored/concede and BTTS calculated individually on that form table just like as I have outlined them, thank you for the efforts made so far in helping to solving my excel challenges. please Pardon me for am still learning

28. ## Re: calculate the goal scored and conceded in the last 6 football Matches

This is my last post:

in X23

=MAX(MAX(IF(\$D\$2:\$D\$20=\$Z23,\$B\$2:\$B\$20)),MAX(IF(\$E\$2:\$E\$20=\$Z23,\$B\$2:\$B\$20)))

Enter with Ctrl+Shift+Enter

As you have 2019 you can use MAXIFS formula instead of the above

This will give the date of the most recent match played

You should now be able to fill in the formulae.

29. ## Re: calculate the goal scored and conceded in the last 6 football Matches

THANk YOU! I appreciate your efforts, these are not want I ask for, sorry for any inconvenience. I will seek other helps too.

30. ## Re: calculate the goal scored and conceded in the last 6 football Matches

Formulae here:

Ab23

=COUNTIFS(\$B\$2:\$B\$20,\$X23,\$D\$2:\$D\$20,\$Z23,\$H\$2:\$H\$20,"H")+COUNTIFS(\$B\$2:\$B\$20,\$X23,\$E\$2:\$E\$20,\$Z23,\$H\$2:\$H\$20,"A")

31. ## Re: calculate the goal scored and conceded in the last 6 football Matches

i really see you want to help, maybe we are having miscommunication here. These Win, Draw, Loss, etc don't add up when I put nth matches played. just like you did formally but it should be on the table individually. Arsenal nth matches summed (Win, Draw, Loss, Goals, etc), Aston villa nth matches summed (Win, Draw, Loss, Goals, etc), then I will be able to drag the formula down.if you don't understand tell me, or maybe you can't help then lemme know as well, you have tried enough friend, you are a genius.

32. ## Re: calculate the goal scored and conceded in the last 6 football Matches

Info for others: the "Nth" will not apply to all teams at any point in time, as some teams will have played less than N; equally some will have played more!.

And is maximumn value of "N" still to be 6?

33. ## Re: calculate the goal scored and conceded in the last 6 football Matches

Yes, the maximum should be 6 or preferably dynamic, for teams that have played less than the maximum nth matches, let the nth number they have played be displayed and summed, since is not up to the maximum (6) nth matches played. Thank you

35. ## Re: calculate the goal scored and conceded in the last 6 football Matches

Thank you, I have manually filled the table (this should be how I wanted it). I will create the Home and Away table once I get these ones correct. In cell AC20, is the nth matches played, I can input or use dropdown there for any recent nth matches I want, if I get it like this, I will try and follow up from there. Thank you once again as I await your response

36. ## Re: calculate the goal scored and conceded in the last 6 football Matches

See attached ("Data2")

HELPER

=LARGE((IF(\$D\$2:\$D\$20=\$Z7,\$B\$2:\$B\$20))+(IF(\$E\$2:\$E\$20=\$Z7,\$B\$2:\$B\$20)),MIN(\$Z\$2,\$AA7))

Entered with Ctrl+Shift+Enter

Still no explanation of last 2 columns

37. ## Re: calculate the goal scored and conceded in the last 6 football Matches

ok, thank you, for the last two columns, "Over 2.5" and "BTTS", Over2.5( is where home team and away team goals scored together is 3+), while BTTS(is where the home team scored 1+ and the away team scored 1+ separately). to explain more on this, Over 2.5 goals is where the match result between home and away team total goals ending in 3 or more goals, while both teams to score(BTTS) is where the home team scored a goal and away team scored a goal too.

38. ## Re: calculate the goal scored and conceded in the last 6 football Matches

THANK YOU SOOO MUCH, I have gone through them and they suit what I needed, for the last two columns, "Over 2.5" and "BTTS", Over2.5( is where home team and away team goals scored together is 3+), and BTTS(is where the home team scored 1+ and the away team scored 1+ separately), Over 2.5 goals is where the match result between home and away team total goals ending in 3 or more goals, and both teams to score(BTTS) is where the home team scored a goal and away team scored a goal.I hope you can help with them too. I appreciate your effort so far

39. ## Re: calculate the goal scored and conceded in the last 6 football Matches

This proposal employs two helper columns (X:Y) which may be moved and/or hidden for aesthetic purposes.
Column X is populated using: =(SUM(F2:G2)>2.5)+0
Column Y is populated using: =AND(F2>0,G2>0)+0
The "over 2.5" and "BTTS" columns are populated using: =SUMPRODUCT((\$B\$2:\$B\$20>=\$Z7)*((\$D\$2:\$D\$20=\$AB7)+(\$E\$2:\$E\$20=\$AB7))*(X\$2:X\$20))
Let us know if you have any questions.

40. ## Re: calculate the goal scored and conceded in the last 6 football Matches

In Ag

=SUMPRODUCT((\$B\$2:\$B\$20>=\$X7)*((\$D\$2:\$D\$20=\$Z7)+(\$E\$2:\$E\$20=\$Z7))*((\$F\$2:\$F\$20+\$G\$2:\$G\$20)>=3))

in AH

=SUMPRODUCT(((\$B\$2:\$B\$20>=\$X7)*(\$D\$2:\$D\$20=\$Z7)*(\$F\$2:\$F\$20>=1)*(\$G\$2:\$G\$20>=1))+((\$E\$2:\$E\$20=\$Z7)*(\$F\$2:\$F\$20>=1)*(\$G\$2:\$G\$20>=1)))

41. ## Re: calculate the goal scored and conceded in the last 6 football Matches

Thank you very much, i will get back to you soon.

42. ## Re: calculate the goal scored and conceded in the last 6 football Matches

Thank you very much,lemme get back to you soon.

43. ## Re: calculate the goal scored and conceded in the last 6 football Matches

Please house, any other steps to take now that I have got my challenge solved?.

44. ## Re: calculate the goal scored and conceded in the last 6 football Matches

Go to "Thread Tools" at top of the page to mark as SOLVED,

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1