Rank based on Multiple Column Criteria

[ravi2628]

Hi Everyone good Morning, Afternoon, Evening, Night

I was working on some data to find the rank based on multiple column criteria, can any one please help me out with the formula.

following data is as follows.

Input

Section	Student Name	Score
A1	BJNNU	245​
A1	DBVDB	234​
A1	HKULJ	116​
A1	HYFBN	211​
A1	JGJO	160​
A1	NNVBJDF	230​
A1	XXCX	262​
A1	ZVBSKJD	245​
A2	ABEHR	325​
A2	ASBA	265​
A2	DFHBWEN	420​
A2	DFN	180​
A2	DN	311​
A2	HWEQH	124​
A2	SBDND	464​
A2	SBHRN	452​
A2	SDNHW	382​
A2	WBHEHW	195​

Output

Section	Student Name	Score	Rank
A1	BJNNU	245​	2​
A1	DBVDB	234​	4​
A1	HKULJ	116​	8​
A1	HYFBN	211​	6​
A1	JGJO	160​	7​
A1	NNVBJDF	230​	5​
A1	XXCX	262​	1​
A1	ZVBSKJD	245​	3​
A2	ABEHR	325​	5​
A2	ASBA	265​	7​
A2	DFHBWEN	420​	3​
A2	DFN	180​	9​
A2	DN	311​	6​
A2	HWEQH	124​	10​
A2	SBDND	464​	1​
A2	SBHRN	452​	2​
A2	SDNHW	382​	4​
A2	WBHEHW	195​	8​

Please help me out with the formula

Thanks & Regards
Ravi

 

[Habtest]

Hi Ravi, see if it works:

Book1
	A	B	C	D
1	Section	Student Name	Score	Rank
2	A1	BJNNU	245	2
3	A1	DBVDB	234	4
4	A1	HKULJ	116	8
5	A1	HYFBN	211	6
6	A1	JGJO	160	7
7	A1	NNVBJDF	230	5
8	A1	XXCX	262	1
9	A1	ZVBSKJD	245	3
10	A2	ABEHR	325	5
11	A2	ASBA	265	7
12	A2	DFHBWEN	420	3
13	A2	DFN	180	9
14	A2	DN	311	6
15	A2	HWEQH	124	10
16	A2	SBDND	464	1
17	A2	SBHRN	452	2
18	A2	SDNHW	382	4
19	A2	WBHEHW	195	8
Sheet1

Cell Formulas
Range	Formula
D2:D19	D2	=COUNTIFS($A$2:$A$19,A2,$C$2:$C$19,">="&C2)-COUNTIFS($A3:$A$20,A2,$C3:$C$20,C2)

 

[ravi2628]

Hi Ravi, see if it works:

Book1
	A	B	C	D
1	Section	Student Name	Score	Rank
2	A1	BJNNU	245	2
3	A1	DBVDB	234	4
4	A1	HKULJ	116	8
5	A1	HYFBN	211	6
6	A1	JGJO	160	7
7	A1	NNVBJDF	230	5
8	A1	XXCX	262	1
9	A1	ZVBSKJD	245	3
10	A2	ABEHR	325	5
11	A2	ASBA	265	7
12	A2	DFHBWEN	420	3
13	A2	DFN	180	9
14	A2	DN	311	6
15	A2	HWEQH	124	10
16	A2	SBDND	464	1
17	A2	SBHRN	452	2
18	A2	SDNHW	382	4
19	A2	WBHEHW	195	8
Sheet1

Cell Formulas
Range	Formula
D2:D19	D2	=COUNTIFS($A$2:$A$19,A2,$C$2:$C$19,">="&C2)-COUNTIFS($A3:$A$20,A2,$C3:$C$20,C2)

Thank for the logic, but if i select entire column rank is starting from "0" so i have added "+1" to the condition.

Thank you,
Ravi

 

[Snakehips]

I think you may get an issue with tied scores?

Maybe just

MRXLMAY21.xlsm
	A	B	C	D
1	Section	Student Name	Score	RANK
2	A1	BJNNU	245	2
3	A1	DBVDB	234	4
4	A1	HKULJ	116	8
5	A1	HYFBN	211	6
6	A1	JGJO	160	7
7	A1	NNVBJDF	230	5
8	A1	XXCX	262	1
9	A1	ZVBSKJD	245	2
10	A2	ABEHR	325	5
11	A2	ASBA	265	7
12	A2	DFHBWEN	420	3
13	A2	DFN	180	9
14	A2	DN	311	6
15	A2	HWEQH	124	10
16	A2	SBDND	464	1
17	A2	SBHRN	452	2
18	A2	SDNHW	382	4
19	A2	WBHEHW	195	8
Result

Cell Formulas
Range	Formula
D2:D19	D2	=COUNTIFS($A$2:$A$19,A2,$C$2:$C$19,">"&C2)+1

 

[ravi2628]

I think you may get an issue with tied scores?

Maybe just

MRXLMAY21.xlsm
	A	B	C	D
1	Section	Student Name	Score	RANK
2	A1	BJNNU	245	2
3	A1	DBVDB	234	4
4	A1	HKULJ	116	8
5	A1	HYFBN	211	6
6	A1	JGJO	160	7
7	A1	NNVBJDF	230	5
8	A1	XXCX	262	1
9	A1	ZVBSKJD	245	2
10	A2	ABEHR	325	5
11	A2	ASBA	265	7
12	A2	DFHBWEN	420	3
13	A2	DFN	180	9
14	A2	DN	311	6
15	A2	HWEQH	124	10
16	A2	SBDND	464	1
17	A2	SBHRN	452	2
18	A2	SDNHW	382	4
19	A2	WBHEHW	195	8
Result

Cell Formulas
Range	Formula
D2:D19	D2	=COUNTIFS($A$2:$A$19,A2,$C$2:$C$19,">"&C2)+1

I have added the +1 to this formula

COUNTIFS($A$2:$A$19,A2,$C$2:$C$19,">="&C2)-COUNTIFS($A3:$A$20,A2,$C3:$C$20,C2)+1