findinf a matching name ans adding up the last 5 entries

**andyh42** · 03-14-2011, 06:49 PM

Hi
I'm trying to add the last 5 weeks goal difference of the team named in cell d2 on the odds sheet with the goal difference information for the same team on gldiff sheet.

Not every team plays each week so if a team does not play then DNP will appear in the cell. Some teams might play 5 times in a 6 week period or more so it needs to be able to tell that.
It does not make a difference if the team is playing home or away.

I've manually put two answers in columns:

Cell D2 on odds sheet: Accrington = 7 in cell F2 this figure in obtained by adding together the figures on sheet gldiff cell H2:B2 which is a seven week period but only played 5 times.
Cell E2 on odds sheet: Birmingham = 0 in cell G2 this figure in obtained by adding together the figures on sheet gldiff cell H8:C8 which is a six week period but only played 5 times.

The sheet gldiff will have new figures added in each week. The new week will always be the last data added to the sheet.

I don't know if it will be of any help but the game week will always be in column A on the odds sheet and row 1 on the gldiff sheet if you need to use it as a starting point for adding up the previous weeks.

I hope someone can help.

**daddylonglegs** · 03-14-2011, 07:16 PM

One way is to use this formula in F2

=SUMIF(gldiff!$B$1:$Z$1,">="&IFERROR(LARGE(IF(ISNUMBER(INDEX(gldiff!$B$2:$Z$100,MATCH(D2,gldiff!$A$2:$A$100,0),0)),gldiff!$B$1:$Z$1),5),1),INDEX(gldiff!$B$2:$Z$100,MATCH(D2,gldiff!$A$2:$A$100,0),0))

confirmed with CTRL+SHIFT+ENTER then copied do G2 and down both columns. If there aren't 5 numbers for that team then all numbers will be summed

**andyh42** · 03-15-2011, 02:30 AM

Hi
Thanks for the speedy reply, I'm afraid it doesn't seem to work it just puts ## and doubles the row size of the cell i've put the formula in. When I press Ctrl+Shift+Enter the formula seems to flick as it is doing somthing then nothing happens. Any ideas.

Cheers

**andyh42** · 03-15-2011, 12:56 PM

Hi daddylonglegs.

I have the forula giving me an answer now thak you, It's not the answer I'm looking for but the information on the gldiff sheet is wrong so ineed to sort that out first and I'll be back intouch if that doesn't sort out the problem. I sorted out the # problem.
Thank you for your time so far.

Regards
andyh42.