Ranking on a Criterion Within a Second Criterion

[chasfh]

I've been searching everywhere for this answer, including on MrExcel, and I am not finding what I am looking for.

This is a simple case of wanting to rank baseball players by one column (Rating) based on their field position within each organization. I do not want to exclude duplicates, which seems to be the problem most answers are trying to solve in examples I have seen. I just want to be able to rank based on the multiple criteria. It's OK if there are ties that result in different "ranks"—in fact, that's preferable for my purposes.

I have included an Excel range through XL2BB below, which includes all players for all positions within two sample organizations. I have already pre-ranked them in the way I want them to make it easier for you to see whether your formula works, but the resulting numbers also have to follow the players properly whenever we re-sort the table.

Suppose I want to see the top three players by position for each organization. The correct formula will rank Tresh, Elberfeld, and Desmond as 1-2-3 among shortstops (SS) in organization 1; Paddack, Hughes, and Kile as 1-2-3 among starting pitchers (SP) in org 1; Rodriguez, Drill, and Shaw as 1-2-3 among catchers (C) in org 2; and so on. You get my drift. That's how you will know you got the formula right, for my purposes.

I appreciate your help in trying to solve this. Thank you.

Rating	Player	Org	Position	Rank
59	Tresh, Tom	1	SS
54	Elberfeld, Kid	1	SS
51	Desmond, Ian	1	SS
38	Riddle, JT	1	SS
36	Smalley Jr., Roy	1	SS
33	Lochhead, Harry	1	SS
30	Kazanski, Ted	1	SS
58	Paddack, Chris	1	SP
56	Hughes, Phil	1	SP
56	Kile, Darryl	1	SP
53	Reynolds, Allie	1	SP
48	Keller, Mitch	1	SP
45	Cabrera, Daniel	1	SP
44	Moret, Roger	1	SP
41	Madden, Kid	1	SP
39	Wood, Mike	1	SP
39	Bautista, Jose	1	SP
39	Nichols, Tricky	1	SP
38	Hernandez, Livan	1	SP
31	Jarvis, Kevin	1	SP
57	Betances, Dellin	1	RP
51	Holder, Jonathan	1	RP
48	Herrera, Kelvin	1	RP
47	Aase, Don	1	RP
43	Brame, Erv	1	RP
40	Castro, Miguel	1	RP
40	Perry, Ryan	1	RP
39	Wilson, Justin	1	RP
37	Stimmel, Archie	1	RP
33	Alvarado, Jose	1	RP
69	Judge, Aaron	1	RF
33	Swoboda, Ron	1	RF
51	Sierra, Ruben	1	LF
49	Kelly, Roberto	1	LF
36	Lough, David	1	LF
35	Arcia, Oswaldo	1	LF
54	De Flatus, Justin	1	CL
37	Reichert, Dan	1	CL
62	Mantle, Mickey	1	CF
43	Grisham, Trent	1	CF
48	d'Arnaud, Travis	1	C
47	Kennedy, Terry	1	C
25	Ritter, Lew	1	C
57	Arenado, Nolan	1	3B
45	Middlebrooks, Will	1	3B
44	Camargo, Johan	1	3B
62	Dugan, Joe	1	2B
50	Randle, Lenny	1	2B
48	Biggio, Craig	1	2B
42	Perez, Tomas	1	2B
39	Ripken, Billy	1	2B
37	Newman, Al	1	2B
34	Stillwell, Kurt	1	2B
29	Johnson, Tim	1	2B
80	Gehrig, Lou	1	1B
64	Pipp, Wally	1	1B
42	Healy, Ryon	1	1B
37	Goldsberry, Gordon	1	1B
35	Traber, Jim	1	1B
35	Tebeau, Patsy	1	1B
50	Ramirez, Alexei	2	SS
39	Thevenow, Tommy	2	SS
34	Rhyne, Hal	2	SS
23	Norman, Nelson	2	SS
70	Potter, Nels	2	SP
68	deGrom, Jacob	2	SP
59	Cooper, Wilbur	2	SP
58	Shore, Ernie	2	SP
55	Breining, Fred	2	SP
49	Nottebart, Don	2	SP
44	Culp, Ray	2	SP
40	Duchscherer, Justin	2	SP
35	Stone, Steve	2	SP
34	Haynes, Jimmy	2	SP
33	Thomas, Myles	2	SP
32	Albury, Vic	2	SP
29	Niekro, Joe	2	SP
28	Leonhard, Dave	2	SP
57	Harvey, Bryan	2	RP
50	Jones, Nate	2	RP
49	Lanning, Johnny	2	RP
48	Ross, Robbie	2	RP
46	Lancaster, Les	2	RP
45	Constable, Jim	2	RP
45	Kolb, Danny	2	RP
41	Bolton, Tom	2	RP
40	Muffett, Billy	2	RP
31	Dickman, Emerson	2	RP
31	Ortiz, Ramon	2	RP
20	Lewis, Jim	2	RP
80	Flagstead, Ira	2	RF
52	Jensen, Jackie	2	RF
48	Unglaub, Bob	2	RF
56	Diaz, Matt	2	LF
49	Goodman, Billy	2	LF
50	Metkovich, George	2	CF
46	Berry, Ken	2	CF
45	Nichols, Reid	2	CF
41	Bradley, Jackie	2	CF
39	Engel, Adam	2	CF
59	Rodriguez, Ellie	2	C
49	Drill, Lew	2	C
48	Shaw, Al	2	C
40	Stephens, Jim	2	C
38	Daley, Pete	2	C
35	Mirabelli, Doug	2	C
31	Kravitz, Danny	2	C
28	Parent, Mark	2	C
80	McGraw, John	2	3B
48	Gilbert, Pete	2	3B
43	Candelario, Jeimer	2	3B
69	Doerr, Bobby	2	2B
56	Engle, Clyde	2	2B
43	Yerkes, Steve	2	2B
39	Holly, Ed	2	2B
36	Schilling, Chuck	2	2B
35	Goliat, Mike	2	2B
29	Young, Bobby	2	2B
52	Gurriel, Yulieski	2	1B
45	Zauchin, Norm	2	1B

 

[Fluff]

How about

=COUNTIFS(D:D,D2,C:C,C2,A:A,">"&A2)+1

 

[chasfh]

How about

=COUNTIFS(D:D,D2,C:C,C2,A:A,">"&A2)+1

Awesome, it totally worked! I haven't figured out how yet, but it totally does! Thanks!

By the way, this is how it looks in my table:

=COUNTIFS([Position2],[@Position2],[Org],[@Org],[oa],">"&[@oa])+1

 

[Fluff]

You're welcome & thanks for the feedback.