Rank formula (based on SUM IF {array})- Challenge for Expert

[Tusharrm]

Dear Exper,

I want to give Rank in attached sheet for two coloumn, i,e in "E" & "F".

1) In colomn E - i want over all ranking based on one set of data i.e. on the basis of Name i.e. in Coloumn C, but the challenge is same name are more then one time in Name colomn, and i want rank based on sum of all same name item.

2) In Colomn F - I want rank for a combination of Class i.e. in colomn B and Name i.e in colomn C, again there

 

[Peter Bartholomew]

Is this what you need?

= 1 + COUNTIFS(
      Table1[Class], Table1[Class],
      Table1[Sum], ">"&Table1[Sum])

 

[Tusharrm]

Is this what you need?

= 1 + COUNTIFS(
      Table1[Class], Table1[Class],
      Table1[Sum], ">"&Table1[Sum])

View attachment 77895

Dear Peter,

The formula you have given for Over all rank is not correct, but it can be utilized "Class and Name wise Rank".
have you convert the same in table? i cant change my data in table, so pl suggest normal formula.

Now over all rank formula need to be reset... but give me normal formula not in table.

 

[Peter Bartholomew]

The calculation produces what I intended so I clearly haven't understood your requirement. Perhaps you could illustrate the result you hope for?
The attached file uses defined names in place of tables and structured references. I do not use direct cell references since describing them as an abomination that has no place in any rational computing environment. Sorry.

This is the formula that I believe needs to be changed

= 1 + COUNTIFS(
      Class, Class,
      Sum, ">"&Sum)

 

[Tusharrm]

The calculation produces what I intended so I clearly haven't understood your requirement. Perhaps you could illustrate the result you hope for?
The attached file uses defined names in place of tables and structured references. I do not use direct cell references since describing them as an abomination that has no place in any rational computing environment. Sorry.

This is the formula that I believe needs to be changed

= 1 + COUNTIFS(
      Class, Class,
      Sum, ">"&Sum)

Dear Peter,

1st of all i am sorry, you are correct i should give sample answer. my apology.

In attached revised sheet i have given my desired answers. in colomn G and H.

I have change bit of data also so pl do not work on old sheet, consider this sheet.

Kindly check colomn G & H which gives my expected answer. ALSO NOTE THAT IF TWO NAME HAVE SAME TOTAL THEN IT SHOULD NOT GIVE SAME RANK ... CHECK RANK OF "ROCK122" AND "ROCK125' WHICH HAVE SAME SUM AMOUNT BUT I GAVE DIFFERENT RANK BASED ON DESCENDING ORDER.

 

[Peter Bartholomew]

I was ranking by the the 'Sum' column rather than the text. I can match your ranking with formulas like

= LET(
    ordered, SORT(UNIQUE(Name)),
    XMATCH(Name,ordered)
  )

but, unless you are using 365 and are happy to use very different programming techniques, I do not think that anything I do will be much use to you. Sorry.

 

[Tusharrm]

I was ranking by the the 'Sum' column rather than the text. I can match your ranking with formulas like

= LET(
    ordered, SORT(UNIQUE(Name)),
    XMATCH(Name,ordered)
  )

but, unless you are using 365 and are happy to use very different programming techniques, I do not think that anything I do will be much use to you. Sorry.
View attachment 77909

Dear Peter,

Yes I am using office 365. U can suggest me a complex formula. I am more happy to use it rather then do it manually every month.

 

[Peter Bartholomew]

That was a surprise; your coding style looked more like Office 2007. That sounds insulting, but it is not intended that way. By far the majority of workbooks do not use newer methods that have entered Excel over the past 10 years or so. I think I would recommend using helper ranges if you have that option, because that will greatly simplify the final result. If your Excel 365 version is one that updates to new code as soon as it becomes available than you will now have the LAMBDA function but that will be totally alien to a traditional Excel author.

= LET(
   Thunkλ,   LAMBDA(x,LAMBDA(x)),
   ClassHdr, TRANSPOSE(SORT(UNIQUE(Class))),
   listϑ,    MAP(ClassHdr, LAMBDA(c, Thunkλ(SORT(UNIQUE(FILTER(Name,Class=c)))))),
   MAP(Name, Class,
      LAMBDA(n,c,
      XMATCH(n, INDEX(listϑ, 1,XMATCH(c,ClassHdr))())
   )))

The above is the formula entitled 'Insanely difficult'. Simplifications are invited!

 

[p45cal]

In E5 only (no copying down):
=XMATCH(C5:C123,UNIQUE(SORT(C5:C123)))
In F5:
=XMATCH(C5,UNIQUE(SORT(FILTER($C$5:$C$123,$B$5:$B$123=B5))))
copy down to the bottom.