Rank results

[Xander78]

Hi,

I'm trying to rank data based on 'Groups' & 'Score'. I've tried using SUMPRODUCT with no success.
There are blank cells in the score column but the formula is labelling them with a rank of 1.
I just need a unique rank 1-? for each group if there's a score with 1 being the best (see table below what im trying to achieve)
The data also contains negative values. Hope someone can assist

=SUMPRODUCT(($A$2:$A$82=A2)*(B2<$B$2:$B$82))+1

[TABLE="width: 322"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Groups[/TD]
[TD]score[/TD]
[TD]rank[/TD]
[/TR]
[TR]
[TD]GROUP 1[/TD]
[TD]-33[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]GROUP 1[/TD]
[TD]60[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]GROUP 1[/TD]
[TD]42[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]GROUP 1[/TD]
[TD]25[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]GROUP 1[/TD]
[TD]44[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]GROUP 1[/TD]
[TD]15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]GROUP 1[/TD]
[TD]-6[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]GROUP 1[/TD]
[TD]10[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]GROUP 2[/TD]
[TD]100[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]GROUP 2[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]GROUP 2[/TD]
[TD]45[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]GROUP 2[/TD]
[TD]53[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]GROUP 2[/TD]
[TD]46[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]GROUP 2[/TD]
[TD]36[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]GROUP 3[/TD]
[TD]0[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]GROUP 3[/TD]
[TD]100[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]GROUP 3[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]GROUP 3[/TD]
[TD]8[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]GROUP 3[/TD]
[TD]62[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]GROUP 3[/TD]
[TD]100[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]GROUP 3[/TD]
[TD]0[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]GROUP 4[/TD]
[TD]25[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]GROUP 4[/TD]
[TD]23[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]GROUP 4[/TD]
[TD]100[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]GROUP 4[/TD]
[TD]20[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]GROUP 4[/TD]
[TD]67[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]GROUP 4[/TD]
[TD]-80[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]GROUP 4[/TD]
[TD]-66[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]GROUP 5[/TD]
[TD]25[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]GROUP 5[/TD]
[TD]0[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]GROUP 5[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]GROUP 5[/TD]
[TD]100[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]GROUP 5[/TD]
[TD]50[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]GROUP 5[/TD]
[TD]40[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]GROUP 5[/TD]
[TD]100[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]GROUP 5[/TD]
[TD]17[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]GROUP 5[/TD]
[TD]0[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]

 

[CyrusTheVirus]

Hi Xander,

Is there a reason why you are unable to remove the blank cells?

Have you looked into the 'GoTo Special' options by pressing F5?