Conditional Rank without duplicates

[alibini]

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

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff.xlsm
	A	B	C
1	name	value	Conditional Rank
2	a	2	1
3	a	2	2
4	a	7	5
5	a	5	3
6	a	5	4
7	a	15	6
8	b	15	3
9	b	15	4
10	b	17	5
11	b	2	1
12	b	11	2
Import

Cell Formulas
Range	Formula
C2:C12	C2	=COUNTIFS(A:A,A2,B:B,"<"&B2)+COUNTIFS(A$2:A2,A2,B$2:B2,B2)

 

[alibini]

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]

Unfortunately I don't know how to do that. Hopefully someone else will step in.

 

[Johnkaust]

Unfortunately I don't know how to do that. Hopefully someone else will step in.

Love to find a solution to this exact issue