Ranking using SUMPRODUCT Conditional Rank and removing ties

[evansons]

H i all,

I have been frustrated by this all day and wondering if someone could help me get my head around this one..

Row A = Territory, B=Rank, C = Customer and D = Bad Debt

Below is a slight illustration of the workbook:

100503 1 12445426 -£ 127.22
100503 2 12340985 -£ 62.52
100503 3 17907145 -£ 52.84
100503 4 12332782 -£ 38.61
100503 5 18683061 -£ 21.68
100503 6 12326282 -£ 6.13
100503 7 18662891 -£ 4.99
100503 8 12371733 -£ 2.41
100503 9 15655159
100503 9 16576630

As you can see rank 9 has two and I need these too be 9 and 10. even though there is no data in the columns I need it showing due to another formula that this links in with later.

The formulas I have i cell B is as follows:

=SUMPRODUCT(--($A$2:$A$10=A2),--(D2>$D$2:$D$10))+1 (realistically there is about 3000 rows but edited the formula to work with the example)

Is there a way I can adpt this formula atall so that it eliminates ties..

Also please note that after 100503 the territory is 100504 and then ranks in the same sort of way and I have the same problem when I get to the bottom.

Thanks in advance Guys