Summarising data between a range of dates issue

PeterG83

New Member
Joined
Feb 8, 2012
Messages
16
Hi there,

I am trying to pull together some simple analysis on performance in a game. Each week we mix up the teams and I have recorded who won, lost, drew etc and have scored people the same way that football teams are scored (3,1,0)

The analysis looks like below. What i want to do though is be able to put in a range of dates in two cells (eg 3/1/2013 to 14/3/2013) and the data will summarise for performance between those dates.
How would i go about doing this? I thought about numbering the 52 weeks 1 to 52 instead of the dates but im still not sure how to do it!

So you know what i have done:
  • the number of number of draws and wins etc i have used countif.
  • N= did not play
  • w=win
  • d=draw
  • L=lost


Thanks
Pete
Quarter
1
1
1
1
1
1
1
1
1
1
1
1
1
2
2
2
2
2
2
2
2
2
2
2
2
2
3
3
3
3
3
3
3
3
3
3
3
3
3
4
4
4
4
4
4
4
4
4
4
4
4
4
Overall Totals
Date
14-Jun
21-Jun
28-Jun
05-Jul
12-Jul
19-Jul
26-Jul
02-Aug
09-Aug
16-Aug
23-Aug
30-Aug
06-Sep
13-Sep
20-Sep
27-Sep
04-Oct
11-Oct
18-Oct
25-Oct
01-Nov
08-Nov
15-Nov
22-Nov
29-Nov
06-Dec
13-Dec
20-Dec
27-Dec
03-Jan
10-Jan
17-Jan
24-Jan
31-Jan
07-Feb
14-Feb
21-Feb
28-Feb
07-Mar
14-Mar
21-Mar
28-Mar
04-Apr
11-Apr
18-Apr
25-Apr
02-May
09-May
16-May
23-May
30-May
06-Jun
Total wins
Total draws
Total losses
DNP
Total games
Should equal game week
Win %
Total points
Average points per game
Ben
N
D
L
N
N
L
L
W
L
L
L
N
W
L
N
N
L
W
W
D
N
L
L
L
L
N
W
N
N
N
L
N
N
N
L
L
5
2
15
14
22
36
22.73%
17
0.77
Chris
N
N
N
N
N
L
N
L
L
W
W
N
N
N
N
N
L
W
N
D
L
N
L
L
L
N
N
N
N
N
W
N
N
N
N
W
5
1
8
22
14
36
35.71%
16
1.14
Sagar
N
D
L
W
N
N
L
W
W
N
L
L
W
N
D
N
N
N
L
D
W
L
W
W
L
N
N
N
N
N
N
N
N
W
W
W
10
3
7
16
20
36
50.00%
33
1.65
Tom M
N
D
W
N
N
W
N
N
W
W
N
W
N
L
D
W
L
N
W
D
L
L
N
N
N
N
N
N
N
N
L
L
N
N
N
N
7
3
6
20
16
36
43.75%
24
1.50

<TBODY>
</TBODY>
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
Hi,

Can you tell me what the first date is please?
14-Jun = 14/06/13 when I copy your data to Excel and your last date 06-Jun copies over as 06/06/13
Should the first date be 14/06/12?

If so, does this work for you?.....

Sample Data...

Excel Workbook
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABB
1Quarter1111111111111222222222222233333333333334444444444444*
2Date14-Jun21-Jun28-Jun05-Jul12-Jul19-Jul26-Jul02-Aug09-Aug16-Aug23-Aug30-Aug06-Sep13-Sep20-Sep27-Sep04-Oct11-Oct18-Oct25-Oct01-Nov08-Nov15-Nov22-Nov29-Nov06-Dec13-Dec20-Dec27-Dec03-Jan10-Jan17-Jan24-Jan31-Jan07-Feb14-Feb21-Feb28-Feb07-Mar14-Mar21-Mar28-Mar04-Apr11-Apr18-Apr25-Apr02-May09-May16-May23-May30-May06-Jun*
3BenNDLNNLLWLLLNWLNNLWWDNLLLLNWNNNLNNNLL*****************
4ChrisNNNNNLNLLWWNNNNNLWNDLNLLLNNNNNWNNNNW*****************
5SagarNDLWNNLWWNLLWNDNNNLDWLWWLNNNNNNNNWWW*****************
6Tom MNDWNNWNNWWNWNLDWLNWDLLNNNNNNNNLLNNNN*****************
7******************************************************
Sheet20


Example Results1...

Excel Workbook
ABCDEFGHIJK
7
8From Date01/06/2012To Date10/06/2013
9
10WDLN
11Overall TotalsTotal WinsTotal drawsTotal lossesDNPTotal GamesShould Equal Game WeekWin %Total PointsAverage Points Per Game
12Ben521514223622.73%170.77
13Chris51822143635.71%161.14
14Sagar103716203650.00%331.65
15Tom M73620163643.75%241.50
16
Sheet20


Example Results2.....

Excel Workbook
ABCDEFGHIJK
7
8From Date01/06/2012To Date13/09/2012
9
10WDLN
11Overall TotalsTotal WinsTotal drawsTotal lossesDNPTotal GamesShould Equal Game WeekWin %Total PointsAverage Points Per Game
12Ben2174101420.00%70.70
13Chris203951440.00%61.20
14Sagar414591444.44%131.44
15Tom M511771471.43%162.29
16
Sheet20



I hope that works for you.

Ak
 
Last edited:

PeterG83

New Member
Joined
Feb 8, 2012
Messages
16
Wow, thanks Akashwani! I will try it and come back to you.

The fist date was the 14th June 2012 and it was the year after that.
 

PeterG83

New Member
Joined
Feb 8, 2012
Messages
16
This works, so thank you very much.

Could you confirm what the -- does in the formula below so that i can fully understand it please.

=SUMPRODUCT(--($B$2:$BA$2>=$B$8)*($B$2:$BA$2<=$D$8)*($A$3:$A$6=$A12)*($B$3:$BA$6=B$10))

Cheers
Pete
 

PeterG83

New Member
Joined
Feb 8, 2012
Messages
16
Thanks Ak.

I have another question.

I want to come up with a summary which shows all the partnerships of people and count wins together, draws together, losses when paired together. How would i go about doing this? The teams are mixed up each week into 5 on each team so a summary of best combinations of pairs would be useful.

Thank you
Pete
 

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911

ADVERTISEMENT

Hi Pete,

From your original data I cannot tell who was paired with who, so, if you can supply a sample of the data that will show the "Pairs" then I'm sure a solution can be found.

Ak
 

PeterG83

New Member
Joined
Feb 8, 2012
Messages
16
Basically there is a pool of players but only 10 people play each week and they are split into 2 teams of 5. So someone may play 3 weeks in a row then doesnt play for a few weeks, it also means some people might play 35 games a season or some might play 45 etc. I was wanting to find some way to show which partnerships of players win the most... Is this possible?

If you need anymore info let me know, hopefully you get where i am coming from!

Thanks
Pete

Players14/06/201221/06/201228/06/201205/07/201212/07/201219/07/201226/07/201202/08/201209/08/201216/08/201223/08/201230/08/201206/09/201213/09/201220/09/201227/09/2012
BenNGDLNNGLLWLLLNWLNN
ChrisNGNNNNGLNLLWWNNNNN
SagarNGDLWNGNLWWNLLWNDN
Tom MNGDWNNGWNNWWNWNLDW
Hugo WSNGNNWNGLWLWLNLLWDL
Pete RNGNNNNGNNNNNNNNNNN
NicoNGNNWNGWWLWLNNWWDN
StefanNGDLLNGNNNNWWLLLDW
NickNGDWNNGNLNNNNNNNNN
GrantNGDWNNGNNNNNNNNNNN
Dan NGNNNNGWLNLNNNWNDL
Pete GNGNWNNGNWLNLWWLWNW
Tom WNGDNLNGLNNNWLNLLDL
DaveNGNWNNGLNNLNNNNWDL
Will TNGNLLNGNNWLWLWNLNN
Johnny BNGNNNNGNNNNNNNNNNN
GavNGNNNNGNWNNNNNNNNN
Hugo SNGNNLNGNNWNNNNLNNW
FreemanNGNNWNGWWWWNWWNNNW
Rowson JrNGNNNNGNNNNNNNNNNN
Matt NGDNWNGWNNNLWNwWDN
MarkNGNNNNGNNNNNNNNNNN
JimmyNGDNNNGNLLNNNNNNNL
Good WillNGNNNNGNNNNNNNNNNN
JohnNGDWNNGNNNNNNNNNDN
RossNGNNLNGNNNNNNNNNNN
Chris SNGNNNNGNNNNNLWNNNN
Chris (Chris S' mate)NGNNNNGNNNNNNLNNNN
Alex HurrelNGNNNNGNNNNNNLNNNN
Jonny OriginalNGNNNNGNNNNNNNNNNN

<COLGROUP><COL style="WIDTH: 159pt; mso-width-source: userset; mso-width-alt: 7753" width=212><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" span=16 width=86><TBODY>
</TBODY>
 

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
Hi Pete,

I'm sorry, but from the above dated I cannot find a way of identifying the partnerships, Eg how many times did Ben, Chris, Sagar, Tom M and Hugo WS appear in the same partnership?

I think that this problem is way beyond my Excel knowledge and you may be best served by creating a new post.

Sorry that I couldn't be of more help to you Pete, but good luck.

Ak
 

Watch MrExcel Video

Forum statistics

Threads
1,109,411
Messages
5,528,623
Members
409,828
Latest member
99DodgeRam

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top