Ranking results using two values and specific criterea

[taubeam]

I have a list of two different results that i have to Rank regionally and nationally

Where Column F (Region) is the region indicator and Column J (TSRs) is the first result and Column K (ACV) is the second result.

For Column J's (TSRs) ranking I will have a great deal of ties. I would like to break the tie with Column K (ACV)

Right now I have =COUNTIFS($F$3:$F$419,$F3,J$3:J$419,">"&$J3)+1 I need to also compare to column K./ Does anyone have an idea?

[TABLE="width: 568"]
<colgroup><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Region[/TD]
[TD]Direct manager indicator[/TD]
[TD]Vertical Manager Indicator[/TD]
[TD]RFPs[/TD]
[TD]TSRs[/TD]
[TD]ACV[/TD]
[/TR]
[TR]
[TD]MW[/TD]
[TD]OA2[/TD]
[TD]PS02[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$0[/TD]
[/TR]
[TR]
[TD]WT[/TD]
[TD]DU1[/TD]
[TD]MS01[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$0[/TD]
[/TR]
[TR]
[TD]WT[/TD]
[TD]DU1[/TD]
[TD]PS01[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$0[/TD]
[/TR]
[TR]
[TD]SC[/TD]
[TD]LA1[/TD]
[TD]MS01[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$6,720[/TD]
[/TR]
[TR]
[TD]SC[/TD]
[TD]IR2[/TD]
[TD]PS01[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$27,720[/TD]
[/TR]
[TR]
[TD]SW[/TD]
[TD]SAN[/TD]
[TD]PS01[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$0[/TD]
[/TR]
[TR]
[TD]SW[/TD]
[TD]DA1[/TD]
[TD]PS01[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]$6,608[/TD]
[/TR]
[TR]
[TD]SE[/TD]
[TD]BR1[/TD]
[TD]FS02[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]$15,116[/TD]
[/TR]
[TR]
[TD]NY[/TD]
[TD]NY3[/TD]
[TD]ZZZZ[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]$34,080[/TD]
[/TR]
[TR]
[TD]NY[/TD]
[TD]NY7[/TD]
[TD]PS02[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$85,680[/TD]
[/TR]
</tbody>[/TABLE]


Please and Thank You

 

[VBA Geek]

is this what you want?

<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L2</th><td style="text-align:left">=RANK(<font color="Blue">J2,$J$2:$J$11,0</font>)+COUNTIFS(<font color="Blue">$K$2:$K$11,">"&K2,$J$2:$J$11,J2</font>)</td></tr></tbody></table></td></tr></table><br />

Excel 2013/2016
	F	G	H	I	J	K	L
1	Region	Direct manager indicator	Vertical Manager Indicator	RFPs	TSRs	ACV	rank
2	MW	OA2	PS02	2	0	$0	7
3	WT	DU1	MS01	2	0	$0	7
4	WT	DU1	PS01	1	0	$0	7
5	SC	LA1	MS01	6	1	$6,720	6
6	SC	IR2	PS01	1	1	$27,720	5
7	SW	SAN	PS01	2	0	$0	7
8	SW	DA1	PS01	4	2	$6,608	3
9	SE	BR1	FS02	2	2	$15,116	2
10	NY	NY3	ZZZZ	3	2	$34,080	1
11	NY	NY7	PS02	9	1	$85,680	4
Sheet3

 

[taubeam]

Yes that will totally work perfectly for my national rank, but I also need to do one based on what their value is in column F.

The one you wrote ranks the whole list together (419 rows) but I need to also rank them among st their region which is indicated in column F

Does that make sense? =COUNTIFS($F$3:$F$419,$F3

 

[VBA Geek]

you mean this way?


=RANK(J2,$J$2:$J$11,0)+COUNTIFS($K$2:$K$11,">"&K2,$J$2:$J$11,J2)+COUNTIFS($K$2:$K$11,K2,$J$2:$J$11,J2,$F$2:$F$11,"<"&F2)

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L
1						Region	Direct manager indicator	Vertical Manager Indicator	RFPs	TSRs	ACV	rank
2						MW	OA2	PS02	2	0	$0	7
3						WT	DU1	MS01	2	0	$0	9
4						WT	DU1	PS01	1	0	$0	9
5						SC	LA1	MS01	6	1	$6,720	6
6						SC	IR2	PS01	1	1	$27,720	5
7						SW	SAN	PS01	2	0	$0	8
8						SW	DA1	PS01	4	2	$6,608	3
9						SE	BR1	FS02	2	2	$15,116	2
10						NY	NY3	ZZZZ	3	2	$34,080	1
11						NY	NY7	PS02	9	1	$85,680	4
Sheet3

 

[taubeam]

Thanks so much for looking at it again, but that is still a national rank

What I'm trying to do is get a rank for each region with out maintaining a table for each region. so the end result would have a 1st, 2nd, or 3rd rank for each region in column F

 

[VBA Geek]

no problem. another try, let's see if I understood it this time around

L2: =1+COUNTIFS($F$2:$F$11,F2,$J$2:$J$11,">"&J2)+COUNTIFS($F$2:$F$11,F2,$J$2:$J$11,J2,$K$2:$K$11,">"&K2)

Excel 2013/2016
	F	G	H	I	J	K	L
1	Region	Direct manager indicator	Vertical Manager Indicator	RFPs	TSRs	ACV	rank
2	MW	OA2	PS02	2	0	$0	1
3	NY	NY3	ZZZZ	3	2	$34,080	1
4	NY	NY7	PS02	9	1	$85,680	2
5	SC	IR2	PS01	1	1	$27,720	1
6	SC	LA1	MS01	6	1	$6,720	2
7	SE	BR1	FS02	2	2	$15,116	1
8	SW	DA1	PS01	4	2	$6,608	1
9	SW	SAN	PS01	2	0	$0	2
10	WT	DU1	MS01	2	0	$0	1
11	WT	DU1	PS01	1	0	$0	1
Sheet3

 

[taubeam]

That worked perfectly!! Thank you so much. This site is spectacular because of people like you.