# Conditional Rank without duplicates

#### alibini

##### New Member
Hi Excel friends,

Looking to solve this problem without using an array formula:

 name value Conditional Rank (formula required to solve for this column) a 2 1 a 2 1 a 7 3 a 5 2 a 5 2 a 15 4 b 15 3 b 15 3 b 17 4 b 2 1 b 11 2

Basically I want a formula to give me Conditional Rank of value based on name without duplicates

The best i can come up with is =SUMPRODUCT((\$A\$2:\$A\$12=A2)*(B2>=\$B\$2:\$B\$12)/COUNTIFS(\$B\$2:\$B\$12,\$B\$2:\$B\$12))
The values it returns are in order but not integers. I'm stumped on this one.
Even better would be a formula that could allow me to use columns rather than ranges, and accommodate blanks.

Thanks Excel friends

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
+Fluff.xlsm
ABC
1namevalueConditional Rank
2a21
3a22
4a75
5a53
6a54
7a156
8b153
9b154
10b175
11b21
12b112
Import
Cell Formulas
RangeFormula
C2:C12C2=COUNTIFS(A:A,A2,B:B,"<"&B2)+COUNTIFS(A\$2:A2,A2,B\$2:B2,B2)

#### alibini

##### New Member
Thanks Fluff, your formula counts all the values. I'm trying to not double count ties, so where I have say 4 unique values within say 6 entries for a name, I want the ranking to count same numbers with the same rank, so that the highest rank is 4. Apologies if I wasn't clear.

#### Fluff

##### MrExcel MVP, Moderator
Unfortunately I don't know how to do that. Hopefully someone else will step in.

1,095,231
Messages
5,443,262
Members
405,220
Latest member
gtgaabaron

### This Week's Hot Topics

• Copy entire row if CountA <>0 to another sheet
[B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
• Select last used Row in Table
I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
• excel workbook: do not allow certain file name
Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
• fixing problem autofilter
hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
• “Weight”
Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
• How to capitalize everything before a certain character?
In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...