Weekly Ranking (per team)

[longlivecoops]

Hello,

I am trying to write a formula that ranks the team members based on their points, per week, within their own teams.

Team Member	Team	Week Beginning	Points
TMA	Team 1	1/01/2015	12
TMB	Team 1	1/01/2015	11
TMC	Team 1	1/01/2015	15
TMD	Team 1	1/01/2015	9
TMA	Team 2	1/01/2015	12
TMB	Team 2	1/01/2015	11
TMC	Team 2	1/01/2015	16
TMD	Team 2	1/01/2015	18
TMA	Team 1	08/01/2015	12
TMB	Team 1	08/01/2015	17
TMC	Team 1	08/01/2015	12
TMD	Team 1	08/01/2015	13
TMA	Team 2	08/01/2015	12
TMB	Team 2	08/01/2015	16
TMC	Team 2	08/01/2015	10
TMD	Team 2	08/01/2015	14

<tbody>
</tbody>

Is this something SUMPRODUCT could achieve? I don't really know how to use it.

Ideally I'd like something I could just drag down as the weeks go on. It's always the same number of team members and teams per week.

Some help would be greatly appreciated.

Thanks

 

[Steve_R]

To make this a bit easier:
1 - I gave your whole sample table range name 'MyData' ($A$1:$D$17)
2 - your points range was named PointsColumn ($D!1:$D17)

First, set up a list of player names. I used a separate sheet for the example

Player	Total Points	Rank
TMA	48	4
TMB	55	1
TMC	53	3
TMD	54	2

<tbody>
</tbody>

For this sheet
The formula to get total points is
=SUMIF(MyData,A2,PointsColumn)

The formula to get the ranking is (NB $B$2:$B$5 is the Total Points column on the new sheet)
=RANK(B2,$B$2:$B$5)

Define your named ranges correctly, you don't have to adjust formula, other than when you add a new player. Define named ranges for the area that ranks players you won't need to do that either.

 

[longlivecoops]

Hi,

Thanks for your reply.

I didn't make it clear enough, but there's a TMA in both team 1 and 2... i.e. there's two TMAs that operate across the two teams. I want a rank of TMA's points within Team 1 and then a rank of TMA's results within Team 2 per week. The TMAs are completely separate. I should've given them different names. My bad. I also need the weeks to remain as they are

Can you think of a formula that'll allow me to do that?

Thanks

 

[Steve_R]

It's my lack of understanding of your problem. What is TMA, TMB, TMC... I took it to be player names. If so, the formula should work except where you have two John Does. There is a chance you will get two John Doe on one team so performing a double column lookup won't solve a duplicate name problem. A better method is to differentiate the names.
Jon Doe
Jon J Doe

If I still misunderstand the problem, Sumifs() will do a two column test

Formula in column C is

=SUMIFS(Points,Team_Member,A1,Team,B1)

Where Points, Team_Member and Team are the same as you data table column headings.

Same method for the rank in column D
=RANK(C1,$C$1:$C$8)

TMA	Team 1	24	6
TMB	Team 1	28	2
TMC	Team 1	27	3
TMD	Team 1	22	8
TMA	Team 2	24	6
TMB	Team 2	27	3
TMC	Team 2	26	5
TMD	Team 2	32	1

<colgroup><col style="width:48pt" span="4" width="64"> </colgroup><tbody>
</tbody>

 

[longlivecoops]

It's my lack of understanding of your problem. What is TMA, TMB, TMC... I took it to be player names. If so, the formula should work except where you have two John Does. There is a chance you will get two John Doe on one team so performing a double column lookup won't solve a duplicate name problem. A better method is to differentiate the names.
Jon Doe
Jon J Doe

If I still misunderstand the problem, Sumifs() will do a two column test

Formula in column C is

=SUMIFS(Points,Team_Member,A1,Team,B1)

Where Points, Team_Member and Team are the same as you data table column headings.

Same method for the rank in column D
=RANK(C1,$C$1:$C$8)

TMA	Team 1	24	6
TMB	Team 1	28	2
TMC	Team 1	27	3
TMD	Team 1	22	8
TMA	Team 2	24	6
TMB	Team 2	27	3
TMC	Team 2	26	5
TMD	Team 2	32	1

<colgroup><col style="width:48pt" span="4" width="64"> </colgroup><tbody>
</tbody>

Thanks mate. I'll give it a whirl when I get into the office on Monday and let you know how I go


Thanks