Rank Question - Ranking by criteria excluding 0

[mat166801]

Hi,

I have been using the following formula to rank successfully =SUMPRODUCT(($A$2:$A$26=$A2)*($C2>$C$2:$C$26))+1 however I would like to only rank numbers above 0.

I have provided a small sample of what result I have been getting with the above formula and in column C, Column D shows the rank result I would like to achieve.

Any help would be greatly appreciated.

Thanks

[TABLE="width: 331"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Group[/TD]
[TD] Sales[/TD]
[TD]Current Rank[/TD]
[TD]Desired Rank[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD] $ 30.00[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD] $ 20.00[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD] $ -[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD] $ 50.00[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]202[/TD]
[TD] $ 10.00[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]202[/TD]
[TD] $ 10.00[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]202[/TD]
[TD] $ -[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]202[/TD]
[TD] $ 30.00[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]202[/TD]
[TD] $ 40.00[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]202[/TD]
[TD] $ 50.00[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]404[/TD]
[TD] $ 10.00[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]404[/TD]
[TD] $ 20.00[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]404[/TD]
[TD] $ 30.00[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]404[/TD]
[TD] $ 40.00[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]404[/TD]
[TD="align: center"] FALSE[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]404[/TD]
[TD] $ -[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

Can't you just subtract 1 from the results?

UPDATE: Scratch that, I 've just seen your results and you cant do that.

 

[bosco_yip]

Desired Rank column, Cell D2 enter formula and copy down :

=IF(N(B2),COUNTIFS(A$2:A$17,A2,B$2:B$17,"<"&B2)+COUNTIFS(A$2:A2,A2,B$2:B2,B2),0)

Regards

 

[gaz_chops]

Try this entered as array (CSE)

=SUMPRODUCT(IF($B$2:$B$26>=1,($A$2:$A$26=$A2)*($B2>$B$2:$B$26)))

 

[mat166801]

Thanks but still not working. What if I get rid of the duplicates and text and just want to exclude 0?

Also this is just a small sample it will be applied to approx 5000 lines and 120 different groups.

[TABLE="width: 331"]
<tbody>[TR]
[TD]Group[/TD]
[TD] Sales[/TD]
[TD]Current Rank[/TD]
[TD]Desired Rank[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD] $ -[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD] $ 20.00[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD] $ 30.00[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD] $ 40.00[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]202[/TD]
[TD] $ 10.00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]202[/TD]
[TD] $ 20.00[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]202[/TD]
[TD] $ 30.00[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]202[/TD]
[TD] $ 40.00[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[gaz_chops]

Maybe

=IF(B2>=1,SUMPRODUCT((($B$2:$B$26>=1)*(--$A$2:$A$26=$A2))*($B2>$B$2:$B$26))+1,0)

 

[mat166801]

Thanks Gaz, worked a treat much appreciated.

 

[gaz_chops]

You're welcome, it appears to work on your original data ok, except where there is a duplicate value, it then returns the same rank for both.