Rank using 3 criteria

[HawkeyeBC]

I have a spreadsheet that tracks historical NCAA tournament data using Kenpom stats. I had a formula that got messed up somehow which was ranking the teams by their "seed rank". Meaning all of the #1 seeds each year would be ranked 1-4, and so on down the list 1-16.

So this year, UConn is the #1 ranked 1 seed, Houston would be #2, etc. What happened in my formula that got it messed up?

Column A is the year
Column B is the Seed # (1-16)
Column C is the overall ranking

Data goes from A2:A1470

=COUNTIFS('All Teams'!$A:$A,'All Teams'!$A2,'All Teams'!$B:$B,'All Teams'!$B2,'All Teams'!$C:$C,">"&'All Teams'!$C2)+COUNTIFS('All Teams'!$A$2:'All Teams'!$A2,'All Teams'!$A2,'All Teams'!$B$2:'All Teams'!$B2,'All Teams'!$B2,'All Teams'!$C$2:'All Teams'!$C2,'All Teams'!$C2)

 

[Fluff]

Can you post some sampled data, along with expected results?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[HawkeyeBC]

Here is what the results should look like

Yr	Seed	Rk	Team	Seed Rk
2024​	1​	1​	Connecticut	1​
2024​	1​	2​	Houston	2​
2024​	1​	3​	Purdue	3​
2024​	4​	4​	Auburn	1​
2024​	2​	5​	Iowa St.	1​
2024​	2​	6​	Arizona	2​
2024​	2​	7​	Tennessee	3​
2024​	4​	8​	Duke	2​
2024​	1​	9​	North Carolina	4​
2024​	3​	10​	Illinois	1​
2024​	3​	11​	Creighton	2​
2024​	2​	12​	Marquette	4​
2024​	4​	13​	Alabama	3​
2024​	3​	14​	Baylor	3​
2024​	3​	19​	Kentucky	4​
2024​	4​	22​	Kansas	4​

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F
1	Yr	Seed	Rk	Team	Seed Rk
2	2024	1	1	Connecticut	1	1
3	2024	1	2	Houston	2	2
4	2024	1	3	Purdue	3	3
5	2024	4	4	Auburn	1	1
6	2024	2	5	Iowa St.	1	1
7	2024	2	6	Arizona	2	2
8	2024	2	7	Tennessee	3	3
9	2024	4	8	Duke	2	2
10	2024	1	9	North Carolina	4	4
11	2024	3	10	Illinois	1	1
12	2024	3	11	Creighton	2	2
13	2024	2	12	Marquette	4	4
14	2024	4	13	Alabama	3	3
15	2024	3	14	Baylor	3	3
16	2024	3	19	Kentucky	4	4
17	2024	4	22	Kansas	4	4
Data

Cell Formulas
Range	Formula
F2:F17	F2	=COUNTIFS(A:A,A2,B:B,B2,C:C,"<"&C2)+1

 

[HawkeyeBC]

That works! Thank you so much. The answer is so simple sometimes.

 

[Fluff]

You're welcome & thanks for the feedback.