Summarising data between a range of dates issue

P

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>
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
MATCH two ranges, column by column
Replies
4
Views
172
Fluff
Fluff
J
  • Question
using sumifs function across multiple sheets
Replies
4
Views
251
KRice
K
J
  • Solved
conditional format to change color
2
Replies
10
Views
196
SanjayGMusafir
SanjayGMusafir
B
  • Question
Pushing Data from Newly Created Sheets
Replies
1
Views
75
bobaol
B
B
  • Question
Data population after adding sheets
Replies
0
Views
105
BijanBorazjani
B

Forum statistics

Threads
1,214,621
Messages
6,120,563
Members
448,972
Latest member
Shantanu2024

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top