Rank within a group & within a sub-group problem

[dleseward]

Using Excel 2010, RANK.EQ works fine for showing a rank within a range. However I also want to work out the rank within a sub-group of the range based on a condition. I can't figure out how to do this, any ideas?

The table below shows what I am after:

Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">13</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D9D9D9;;">Individual</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D9D9D9;;">Group</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D9D9D9;;">Score</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D9D9D9;;">Group Rank</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D9D9D9;;">Overall Rank</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D9D9D9;;">Comment</td></tr><tr><td style="color: #161120;text-align: center;">14</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Person 1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">100</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1st overall & 1st within Group A</td></tr><tr><td style="color: #161120;text-align: center;">15</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Person 2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">95</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3rd overall but 2nd within Group A</td></tr><tr><td style="color: #161120;text-align: center;">16</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Person 3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">B</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">98</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2nd overall but 1st within Group B</td></tr><tr><td style="color: #161120;text-align: center;">17</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Person 4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">90</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr><td style="color: #161120;text-align: center;">18</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Person 5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">B</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">89</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr><td style="color: #161120;text-align: center;">19</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Person 6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">B</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">80</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr><td style="color: #161120;text-align: center;">20</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Person 7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">91</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr><td style="color: #161120;text-align: center;">21</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Person 8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">70</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">10</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr><td style="color: #161120;text-align: center;">22</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Person 9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">85</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr><td style="color: #161120;text-align: center;">23</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Person 10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">80</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table>

Sheet1​

Within the example I want to know the overall rank of each person plus their rank within the Group they belong to.

I think this requires an array formula, trying the following:
=RANK.EQ($C2,IF($B$2:$B$11=$B2,$C$2:$C$11,0),0) as an array formula seems to generate a correct array from the IF statement (ie. {100;95;0;90;0;0;0;70;0;0}), but the result is then a #VALUE error. This seems to be a valid input to the RANK.EQ function, so I am puzzled.

Thanks.

PS. Incidentally I used the MrExcelHTML addin to add the example, so it seem to work fine in Excel 2010.

 

[dleseward]

Doing some more trawling post by post, I found a post
http://www.mrexcel.com/forum/showthread.php?t=529590&highlight=rank+array+formula that had a similar problem & solution posted by Ron Coderre.

While the solution in that post did not work for me (it generates 1 for all rows), tinkering with it and much re-running of the Evaluate option, yielded the following array formula that seems to work:

=MATCH(C33,INDEX(LARGE(($C$33:$C$43)*($B$33:$B$43=B33),ROW(INDIRECT("1:"&COUNTIF($B$33:$B$43,B33)))),0),0)

So thanks to Ron, although finding his post was almost an accident!

I am still curious why my original use of RANK.EQ within an array formula doesn't work. This solution works, but it seems like a hairy formula!

cheers,
David

 

[Peter_SSs]

Does this also work for you?

Excel Workbook
	B	C	D
13	Group	Score	Group Rank
14	A	100	1
15	A	95	2
16	B	98	1
17	A	90	3
18	B	89	2
19	B	80	3
20	C	91	1
21	A	70	4
22	C	85	2
23	C	80	3
Rank in Groups

 

[dleseward]

Peter,

Yes that works very well.

I am now trying it for a slightly more complex situation, where I want to check the rank across a non-contiguous range (as below where I want to rank across Individuals & Teams). Can I modify the SUMPRODUCT formula to cover this situation?

Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">32</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D9D9D9;;">Individual</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D9D9D9;;">Group</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D9D9D9;;">Score</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D9D9D9;;">Group Rank</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D9D9D9;;">Overall Rank</td><td style="font-weight: bold;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D9D9D9;;">Comment</td></tr><tr><td style="color: #161120;text-align: center;">33</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Person 1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">100</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1st overall & 1st within Group A</td></tr><tr><td style="color: #161120;text-align: center;">34</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Person 2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">95</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">5th overall but 3rd within Group A (across Inidividuals & Teams)</td></tr><tr><td style="color: #161120;text-align: center;">35</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Person 3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">B</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">98</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td></tr><tr><td style="color: #161120;text-align: center;">36</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Person 4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">90</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td></tr><tr><td style="color: #161120;text-align: center;">37</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Person 5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">B</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">89</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td></tr><tr><td style="color: #161120;text-align: center;">38</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Person 6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">B</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">80</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">11</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td></tr><tr><td style="color: #161120;text-align: center;">39</td><td style="text-align: right;border-top: 1px solid black;;">
</td><td style="text-align: right;border-top: 1px solid black;;">
</td><td style="text-align: right;border-top: 1px solid black;;">
</td><td style="text-align: right;border-top: 1px solid black;;">
</td><td style="text-align: right;border-top: 1px solid black;;">
</td><td style="text-align: right;border-top: 1px solid black;;">
</td></tr><tr><td style="color: #161120;text-align: center;">40</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">41</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D9D9D9;;">Team</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D9D9D9;;">Group</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D9D9D9;;">Score</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D9D9D9;;">Group Rank</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D9D9D9;;">Overall Rank</td><td style="font-weight: bold;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D9D9D9;;">Comment</td></tr><tr><td style="color: #161120;text-align: center;">42</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Team 1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">99</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td></tr><tr><td style="color: #161120;text-align: center;">43</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Team 2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">94</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td></tr><tr><td style="color: #161120;text-align: center;">44</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Team 3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">B</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">97</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td></tr><tr><td style="color: #161120;text-align: center;">45</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Team 4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">89</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td></tr><tr><td style="color: #161120;text-align: center;">46</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Team 5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">88</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">10</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td></tr></tbody></table>

Sheet1​

thanks,
David

 

[dleseward]

Should have applied my brain, the following seems to work just fine:

=SUMPRODUCT(($B$33:$B$38=B33)*(C33<$C$33:$C$38))+SUMPRODUCT(($B$42:$B$46=B33)*(C33<$C$42:$C$46))+1

So thanks again for the inspiration.

Incidentally I am actually using it across 2 Tables with structured references (ie. Table1[col1]) instead of the ranges and it works.

thanks again,
David

 

[Peter_SSs]

Perhaps I have misunderstood your description of what you are trying to achieve, but looking at the Group Rank, why for example ..

- are row 42 (Team 1, Group A, 99) and row 43 (Team 2, Group A, 94) both ranked '2'?

- is row 45 (Team 4, Group A, 89, rank 4) ranked higher than row 36 (Person 4, Group A, 90, rank 5)?

From my way of thinking just extending the ranges in my previous formula may be what you want?

Excel Workbook
	A	B	C	D
32	Individual	Group	Score	Group Rank
33	Person 1	A	100	1
34	Person 2	A	95	3
35	Person 3	B	98	1
36	Person 4	A	90	5
37	Person 5	B	89	3
38	Person 6	B	80	4
39
40
41	Team	Group	Score	Group Rank
42	Team 1	A	99	2
43	Team 2	A	94	4
44	Team 3	B	97	2
45	Team 4	A	89	6
46	Team 5	C	88	1
Rank in Groups (2)

 

[dleseward]

Actually the following variation of your formula worked:

=SUMPRODUCT(($B$33:$B$38=B33)*(C33<$C$33:$C$38))+SUMPRODUCT(($B$42:$B$46=B33)*(C33<$C$42:$C$46))+1

The idea was to include both Individuals and Teams in assessing the Group rank. In reality I am ranking Projects & Programmes and of course the data is in 2 different tables though they have some common fields.

thanks again,

 

[FBPER]

Read this thread but not sure I fully understand the solution.

I'm looking to use Rank.EQ but need to have a condition applied to it. Say I want to rank NFL teams by their number of points scored in each season, and I have 60 seasons in a file, so I don't want to have to do it manually.

Say the team is in column B, the year is in column C, and the points scored number that I want to rank by is in column D. I'll put the rank in column E.

Now I could just sort first by year and then by points scored, and put an if statement into a cell in E3 that reads: =IF(C3=C2,E2+1,1). As long as I put the number 1 in cell E2, that works well.

The issue is if there are ties. In that case, rank won't work perfectly. So using Rank.EQ would be a nice fit. But I can't seem to figure out how to use Rank.EQ *and* account for the fact that there are 60 seasons worth of data. Any thoughts?

 

[pradeepronaldo]

How to we get the ranking derived from SUMPRODUCT in ascending order. I have used sumproduct and its giving me ranking in descending order.

this is the formula am using:
=SUMPRODUCT(($D:$D=D2)*($X:$X>X2))+1

its giving me rank for different grouping of a column but in default order i.e. descending , i need it in ascending order.

Help please

 

[mikerickson]

With the data as in post #6

How about =COUNTIFS($A$2:$A$11,A2, $B$2:$B$11,">="&B2)