rank function tie breaker

[nikhil0311]

Hello Friends, In below example, I am calculating rank in column A based on revenue in column F using criteria product = Wire and stage = Open.

I want all such clients with same key has to be ranked differently when there is a revenue tie i.e. In this example, row 5,6,8 have same revenue i.e. $6mm so they should be ranked 3,4,5 instead of 3,3,3.

Thanks a lot in advance!!

rank function.xlsx
	A	B	C	D	E	F
1	Rank	Key	stage	product	client	revenue ($mm)
2	1	openWire	open	Wire	A	10
3	6	openWire	open	Wire	B	5
4	2	openWire	open	Wire	C	7
5	3	openWire	open	Wire	D	6
6	3	openWire	open	Wire	E	6
7	1	closeWire	close	Wire	G	7
8	3	openWire	open	Wire	F	6
9	1	progressWire	progress	Wire	H	9
10	1	lostWire	lost	Wire	I	16
Sheet1

Cell Formulas
Range	Formula
A2:A10	A2	=COUNTIFS($B$2:$B$10,B2,$F$2:$F$10,">"&F2)+1
B2:B10	B2	=CONCAT(C2:D2)

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F
1	Rank	Key	stage	product	client	revenue ($mm)
2	1	openWire	open	Wire	A	10
3	6	openWire	open	Wire	B	5
4	2	openWire	open	Wire	C	7
5	3	openWire	open	Wire	D	6
6	4	openWire	open	Wire	E	6
7	1	closeWire	close	Wire	G	7
8	5	openWire	open	Wire	F	6
9	1	progressWire	progress	Wire	H	9
10	1	lostWire	lost	Wire	I	16
Data

Cell Formulas
Range	Formula
A2:A10	A2	=COUNTIFS($B$2:$B$10,B2,$F$2:$F$10,">"&F2)+COUNTIFS(B$2:B2,B2,F$2:F2,F2)
B2:B10	B2	=CONCAT(C2:D2)

 

[nikhil0311]

How about

Fluff.xlsm
	A	B	C	D	E	F
1	Rank	Key	stage	product	client	revenue ($mm)
2	1	openWire	open	Wire	A	10
3	6	openWire	open	Wire	B	5
4	2	openWire	open	Wire	C	7
5	3	openWire	open	Wire	D	6
6	4	openWire	open	Wire	E	6
7	1	closeWire	close	Wire	G	7
8	5	openWire	open	Wire	F	6
9	1	progressWire	progress	Wire	H	9
10	1	lostWire	lost	Wire	I	16
Data

Cell Formulas
Range	Formula
A2:A10	A2	=COUNTIFS($B$2:$B$10,B2,$F$2:$F$10,">"&F2)+COUNTIFS(B$2:B2,B2,F$2:F2,F2)
B2:B10	B2	=CONCAT(C2:D2)

This works like a charm, thanks a lot , Sir

 

[Fluff]

You're welcome & thanks for the feedback.