Ranking using SUMPRODUCT Conditional Rank and removing ties

**evansons** · 03-22-2011, 03:18 PM

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

**DonkeyOte** · 03-23-2011, 03:42 AM

You could append each value with a unique number (based on ROW)

Please Login or Register  to view this content.

Note however that SUMPRODUCT is not very efficient and used en masse is likely to lead to a slowdown in performance.

If your data is sorted as implied (By Territory and Bad Debt) then you can use a far more trivial approach than the above:

Please Login or Register  to view this content.

Sorting data sets is often the key to really efficient calculations.

**evansons** · 03-23-2011, 05:17 AM

Thank you very much; this was very useful

**Melvinrobb** · 07-24-2012, 10:48 AM

Not sure if I should be creating a new post, but my question is related specifically to this same scenario.
instead of using sumproduct, i used the countifs formula to determine conditional ranking:

Formula:

Please Login or Register  to view this content.

Is there any way to make the countif function rank 1,2,3 instead of 1,1,3 for tied values?
You would avoid the slowdown in performance that goes with the sumproduct function, while not needing to sort the data prior.
I also may have a "*" in cells AH5 or AI5, and I BELIEVE the countifs functions will recognize "*"s as being a wildcard, while the sumproduct function will not.

**Cutter** · 07-24-2012, 01:00 PM

Melvinrobb, you should have gone with your instinct:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.