Team's scored goals

[juskaa]

Hi, I have spreadsheet where is league Serie A results. Is there any function that gives me team for example "Juventus" scored goals in last five games.
Screenshot of spreadsheet in link.
http://pasteboard.co/iviKjt391.png

[FDibbins]

Hi, welcome to the forum

Please upload your file to the forum. Not all members are able - or willing - to access file-hosting sites
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[juskaa]

Thank you. Here is the file.

[JeteMc]

Assuming that you will want a list of all the teams the following array entered formula* is used:

=INDIRECT(TEXT(MIN(IF((B$2:C$1141<>"")*(COUNTIF(H$1:H1,B$2:C$1141)=0),ROW(B$2:C$1141)*100+COLUMN(B$2:C$1141),6553601)),"R0C00"),0)&""

The following array entered formula* identifies the row in which the first of the last five games is recorded (this column may be hidden for aesthetic purposes):

=IFERROR(SMALL(IFERROR(ROW(A$2:A$1141)/((B$2:B$1141=H2)+(C$2:C$1141=H2)),FALSE),5),"")

The following formula will display the count of goals made by the team either at home or away, for their last five games:

=IFERROR(SUMPRODUCT((INDIRECT("B2:"&ADDRESS(I2,2))=H2)*INDIRECT("D2:"&ADDRESS(I2,4))+(INDIRECT("C2:"&ADDRESS(I2,3))=H2)*INDIRECT("E2:"&ADDRESS(I2,5))),"")

Let us know if you have any questions.

[juskaa]

Thank you very much for helping! Added reputation.

[JeteMc]

You're Welcome. Thank you for the added reputation and for marking the thread as 'Solved'. I hope that you have a blessed day.

[juskaa]

I tried little bit develop my Serie A - calculator. My idea is that the calculation will happen in every row, where is a game played and then I just can scroll down the function, when I add new games.

I succeeded to add a row counter, which counts the row in which the first of the last five games is recorded. But now I can't count team x scored goal in last 5 games. I think that the calculation would work with SUM.IF - style functions. I tried this function, but I think the "ROW" part is added wrong:

=SUMPRODUCT((B2:B1141=b2)*D2:D199*(ROW(2:1141)>G2))+SUMPRODUCT((C2:C1141=b2)*E2:E199*(ROW(2:1901)>G2))

The file is in attachments.

[JeteMc]

If you are looking for the number of goals scored in the last five home games then use the following formulas.
In G2*:

=IFERROR(SMALL(IFERROR(ROW($A$2:$A$1141)/($B$2:$B$1141=B2),FALSE),$M$2),"")

*This is an array entered formula, so that after pasting this into G2 simultaneously press the Ctrl, Shift and Enter keys, before copying the formula down the column.
In H2:

=IFERROR(SUMPRODUCT((INDIRECT("B2:"&ADDRESS(G2,2))=B2)*INDIRECT("D2:"&ADDRESS(G2,4))),"")

Let us know if you have any questions.

[juskaa]

If we look my latest upload file and we take one game for example. Let's look the game which is in row 28: Palermo vs. Sampdoria which ended 2-0 for home.

In cell G28 I have row helper number "74" which tells where the last of the latest five games is recorded.

So between rows 29-74 Palermo had played five games and my goal is to get in cell H28 that how many goals Palermo scored in their latest five games which were between rows 29-74.

With this function, I get the row helper number, which tells where last of the latest five games is recorded. So in G28 I have:

=IFERROR(SMALL(IFERROR(ROW(A29:$A$1141)/((B29:$B$1141=B28)+(C29:$C$1141=B28));FALSE);$M$2);"")

And in a cell H28 I have this function, which should tell Palermo's scored goals in games which are between rows 29-74:

=IFERROR(SUMPRODUCT((INDIRECT("B2:"&ADDRESS(G28;2))=B28)*INDIRECT("D2:"&ADDRESS(G28;4))+(INDIRECT("C2:"&ADDRESS(G28;3))=B28)*INDIRECT("E2:"&ADDRESS(G28;5)));"")

Now that function tells, how many goals Palermo have scored between games which are in rows 1-74! And my goal is to get that how many goals they've made in games between rows 29-74. That function needs little bit change, but my Excel skills can't do that.

[JeteMc]

If I understand correctly you want the sum of the home team's scores in the current and previous four games, whether played at home or away. If that is a correct interpretation then the following array entered formula will yield that result providing another helper column is employed:

=IFERROR(SUMPRODUCT((INDIRECT(ADDRESS(H2,2)&":"&ADDRESS(G2,2))=B2)*INDIRECT(ADDRESS(H2,4)&":"&ADDRESS(G2,4))+(INDIRECT(ADDRESS(H2,3)&":"&ADDRESS(G2,3))=B2)*INDIRECT(ADDRESS(H2,5)&":"&ADDRESS(G2,5))),"")

The helper column, H, is populated with the formula: =ROW()
Let us know if you have any questions.

[juskaa]

Thank you for you help. Spreadsheet is working now!

[JeteMc]

You're Welcome, I am glad that it now works as anticipated. I hope that you have a blessed day.