lookup formula

[carlos50]

I have a spreadsheet of soccer results. I would like to create a lookup formula of some kind that sums the goals scored by a team in the last five matches. So it should somehow be based on the date, as it should only include the five most recent matches, not matches played before that.

Any suggestions?

[6StringJazzer]

You don't really need a lookup, because you are not searching for a value. You can probably do this with some combination of SUMIF, COUNT, INDEX. But we need to know a little more about how your data is organized. Does your workbook deal with multiple teams? What data, and what columns is it in?

I am not sure your title matches your question; you might consider changing it to something like "Sum five most recent events".

[zbor]

Basically something like: =SUMIFS(C1:C20;A1:A20;">=10.3.2010";B1:B20;"a")

But this has several problems: you need to enter 5th dat manualy (of course, you can use some aditional cells like D1 for date and D2 for club)

=SUMIFS(C1:C20;A1:A20;">="&D1;B1:B20;D2)

But best way would be that you upload some example workbook with some example data

[carlos50]

Thx for the replys. I am not at my home comp at the moment. I will upload an example workbook sometime tonight.

[carlos50]

Here is my workbook, cleansed of unnecesary data.

For any given match, I would like to sum up each teams scored goals in the five most recent matches.

[DonkeyOte]

see this virtually identical thread from a few days ago: http://www.excelforum.com/excel-prog...lculation.html

(there is an attachment in one of the last posts showing a formulae based approach)

[carlos50]

Thank you, that was just what I was looking for and for the same purpose.

For some reason, I cant get it to work on my spreadsheet though. I have uploaded my try, please check what I am doing wrong. It seems to me that the columns L and M are messed up, I just cant figure out why.

[DonkeyOte]

The other thread was based on results in the reverse order to yours - ie most recent listed first.

For the opposite - using your file:

Please Login or Register  to view this content.

Note L & M are both Arrays... as per instructions you must ensure you commit using CTRL + SHIFT + ENTER as opposed to Enter as per "normal" formulae.

[carlos50]

Thanks I got it working now. The last result of the other post was in the same order as mine though.

One last thing I only want it to show a result if the team has actually played 6 matches and otherwise it should show False or something similar. Or should I post this in a new thread?

[DonkeyOte]

I would suggest you modify the formulae in L & M such that less than 6 results generates an error .... so replace:

MIN(6;COUNTIF($B3:$C$500;$B2))

with

6

in both L & M formulae - remember to reset the Arrays with CTRL + SHIFT + ENTER once modified.


Note that there is an error in the previously suggested formula will need to be corrected, I2 should read:

ISNUMBER($M2)

and not L2 as is presently the case.

[carlos50]

It works great, thanks a lot!!! i consider the subject solved.