-1
Name   Mathematics  Science  Biology  History  Total Marks  Rank
A          90          91       95      90          366       1
B          85          95       90      95          365       2
C          98          80       80      85          343       3
D          90          80       85      88          343       3
E          99          83       80      81          343       3

Hence person C, D & E got same total marks 343 and ranking of them is 3. Since E, C & D got 99, 98 & 90 respectively in mathematics, I want to rank as E = 3, C = 4 & D =5.

I badly need the solution. Help me please.

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
Najmul Hossain
  • 13
  • 2

2 Answers2

2

As you are on Excel365 then try below formula.

=XMATCH(F2*100000+B2,SORT($F$2:$F$6*100000+$B$2:$B$6,,-1),0)

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • IF D and E both got 90 in math and same total mark 343 then there rank would be same (4). If i want unique rank then how can it be solved? Hence i want D = 4, and E =5 since D is prior to E & already C = 3. – Najmul Hossain Sep 13 '21 at 12:23
  • Then you have to consider another subject. May be you can add english subject column to formula. – Harun24hr Sep 13 '21 at 16:29
  • If both of them got equal number in all subject and I want unique rank according to their serial, what will be the formula – Najmul Hossain Sep 14 '21 at 10:07
0

Another option which you have in Excel 365 is to use Sortby:

=LET(seq,SEQUENCE(ROWS(A2:A6)),XMATCH(seq,SORTBY(seq,F2:F6,-1,B2:B6,-1)))

enter image description here

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37