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

A	B	C	D
Group	Sales	Current Rank	Desired Rank
101	$ 30.00	3	2
101	$ 20.00	2	1
101	$ -	1	0
101	$ 50.00	4	3
202	$ 10.00	2	1
202	$ 10.00	2	2
202	$ -	1	0
202	$ 30.00	4	3
202	$ 40.00	5	4
202	$ 50.00	6	5
404	$ 10.00	2	1
404	$ 20.00	3	2
404	$ 30.00	4	3
404	$ 40.00	5	4
404	FALSE	6	0
404	$ -	1	0

<tbody>
</tbody>

 

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

Group	Sales	Current Rank	Desired Rank
101	$ -	1	0
101	$ 20.00	2	1
101	$ 30.00	3	2
101	$ 40.00	4	3
202	$ 10.00	1	1
202	$ 20.00	2	2
202	$ 30.00	3	3
202	$ 40.00	4	4

<tbody>
</tbody>

 

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