# Summarising data between a range of dates issue

#### PeterG83

##### New Member
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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
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:
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.

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

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

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

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

 Players 14/06/2012 21/06/2012 28/06/2012 05/07/2012 12/07/2012 19/07/2012 26/07/2012 02/08/2012 09/08/2012 16/08/2012 23/08/2012 30/08/2012 06/09/2012 13/09/2012 20/09/2012 27/09/2012 Ben NG D L N NG L L W L L L N W L N N Chris NG N N N NG L N L L W W N N N N N Sagar NG D L W NG N L W W N L L W N D N Tom M NG D W N NG W N N W W N W N L D W Hugo WS NG N N W NG L W L W L N L L W D L Pete R NG N N N NG N N N N N N N N N N N Nico NG N N W NG W W L W L N N W W D N Stefan NG D L L NG N N N N W W L L L D W Nick NG D W N NG N L N N N N N N N N N Grant NG D W N NG N N N N N N N N N N N Dan NG N N N NG W L N L N N N W N D L Pete G NG N W N NG N W L N L W W L W N W Tom W NG D N L NG L N N N W L N L L D L Dave NG N W N NG L N N L N N N N W D L Will T NG N L L NG N N W L W L W N L N N Johnny B NG N N N NG N N N N N N N N N N N Gav NG N N N NG N W N N N N N N N N N Hugo S NG N N L NG N N W N N N N L N N W Freeman NG N N W NG W W W W N W W N N N W Rowson Jr NG N N N NG N N N N N N N N N N N Matt NG D N W NG W N N N L W N w W D N Mark NG N N N NG N N N N N N N N N N N Jimmy NG D N N NG N L L N N N N N N N L Good Will NG N N N NG N N N N N N N N N N N John NG D W N NG N N N N N N N N N D N Ross NG N N L NG N N N N N N N N N N N Chris S NG N N N NG N N N N N L W N N N N Chris (Chris S' mate) NG N N N NG N N N N N N L N N N N Alex Hurrel NG N N N NG N N N N N N L N N N N Jonny Original NG N N N NG N N N N N N N N N N N

<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>

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

No problem, thanks AK.

I have posted this question seperately.

Pete

Replies
3
Views
101
Replies
2
Views
61
Replies
8
Views
206
Replies
0
Views
42
Replies
1
Views
126

1,203,264
Messages
6,054,449
Members
444,725
Latest member

### 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.

### Which adblocker are you using?

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

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