Need Help with Tricky Formula - Looking up last x entries and returning a count

[kylekran]

Hi,

I Have a list of NHL game results and I am looking for a formula to display the # of wins in the last 10 games played by a particular team. Problems are that the teams to play according to any predictable schedule and I need to lookup the last 10 from the bottom of the array. Not really sure how to even start this one or I would show what I have here....

Here is the worksheet:

Excel 2007
	A	B	C	D	E	F
3	Date	Team	Home or Away?	Score	Win or Lose?	#Wins in last 10 games
4	Date	10/7/2015
5	7-Oct	Montreal	H	3	W
6	N	Toronto	V	1	L
7	7-Oct	NyRangers	H	3	W
8	N	Chicago	V	2	L
9	7-Oct	Vancouver	H	5	W
10	N	Calgary	V	1	L
11	7-Oct	SanJose	H	5	W
12	N	LosAngeles	V	1	L
13	Date	10/8/2015
14	8-Oct	Ottawa	H	3	W
15	N	Buffalo	V	1	L
16	8-Oct	Winnipeg	H	6	W
17	N	Boston	V	2	L
18	8-Oct	Philadephi	H	2	L
19	N	TampaBay	V	3	W
20	8-Oct	Edmonton	H	1	L
21	N	StLouis	V	3	W
22	8-Oct	Carolina	H	1	L
23	N	Nashville	V	2	W
24	8-Oct	Pittsburgh	H	0	L
25	N	Dallas	V	3	W
26	8-Oct	Minnesota	H	5	W
27	N	Colorado	V	4	L
28	Date	10/9/2015
29	9-Oct	Winnipeg	H	3	W
30	N	NewJersey	V	1	L
31	9-Oct	NyRangers	H	4	W
32	N	Columbus	V	2	L
33	9-Oct	Chicago	H	3	W
34	N	NyIslander	V	2	L
Sheet1

Thanks for the help!

 

[Aladin Akyurek]

Try to select a particular team and tell what its score is for guidance.

 

[kylekran]

In this example I filtered by team Chicago. I manually typed in the result I want to get in column F.

The number in column F represents the number of "W"'s in Column E corresponding to the previous 10 entries where the team in column B is Chicago.\

Excel 2007
	A	B	C	D	E	F
3	Date	Team	Home or Away?	Score	Win or Lose?	#Wins in last 10 games
8	N	Chicago	V	2	L	X
33	9-Oct	Chicago	H	3	W	X
59	N	Chicago	V	4	W	X
104	14-Oct	Chicago	H	0	L	X
115	15-Oct	Chicago	H	1	L	X
158	N	Chicago	V	4	W	X
213	N	Chicago	V	3	W	X
249	N	Chicago	V	1	W	X
269	N	Chicago	V	1	W	X
307	29-Oct	Chicago	H	1	L	X
328	30-Oct	Chicago	H	4	L	6
370	N	Chicago	V	4	W	6
396	N	Chicago	V	5	L	6
421	6-Nov	Chicago	H	2	L	5
464	N	Chicago	V	4	W	5
516	N	Chicago	V	2	L	6
555	14-Nov	Chicago	H	4	W	5
563	N	Chicago	V	4	W	5
Sheet1

Let me know if that makes things any more clear.

thanks!

 

[Aladin Akyurek]

No, it doesn't. I'd rather have Team = Chicago, N number of wins, where N = 7 or whatever given the original sample you posted.