Sumproduct for Ranking

I have a formula in excel which evaluates a range of cells then provides a ranking without skipping numbers (1, 2, 3...etc.) Additionally, duplicate values are assigned the same rank, I need this.

I've tried playing around with the RANKEQ formula but some rank numbers are being skipped.

Excel Formula:

=SUMPRODUCT(($R$5:$R$1615<$R5)/COUNTIF($R$5:$R$1615,$R$5:$R$1615))+1

Here's the formula I'm using which works, except for skipping rank numbers...

Smartsheet Formula:

=RANKEQ([AMYR]@row, [AMYR]:[AMYR]) + COUNTIF([AMYR]@row:[AMYR]@row, RANKEQ(@cell, [AMYR]:[AMYR]) = RANKEQ([AMYR]@row, [AMYR]:[AMYR])) - 1

Here's an example of what I'm seeing, sorted highest value:

Any suggestions would be greatly appreciated. Thanks!

Answers

  • Hi @Eric G.

    Here's another Community thread that discusses Ranking without Duplicating Ranks.

    One of the solutions looks very similar to yours, with a couple of tweaks.

    The COUNTIF at the end of your formula should be COUNTIFS with an S, and you will need to look for if [AMYR]:[AMYR] is not blank (versus your range of [AMYR]@row:[AMYR]@row).

    You would also need to add the system-generated auto-number column [Row ID] as a helper column. I've copied over the formula from that post:

    =RANKEQ(A@row, A:A) + COUNTIFS(A:A, ISNUMBER(@cell), [Row ID]:[Row ID], <=[Row ID]@row, A:A, RANKEQ(@cell, A:A) = RANKEQ(A@row, A:A)) - 1)

    Here's how to adjust your formula to match this structure:

    =RANKEQ([AMYR]@row, [AMYR]:[AMYR]) + COUNTIFS([AMYR]:[AMYR], <> "", [Row ID]:[Row ID], <=[Row ID]@row, RANKEQ(@cell, [AMYR]:[AMYR]) = RANKEQ([AMYR]@row, [AMYR]:[AMYR])) - 1


    Let me know if this works!

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Eric G
    Eric G ✭✭✭

    Hi @Genevieve P.!

    Sorry for the delay, I put this project on the back burner but now picking back up where I left off.

    Thank you for the suggestion, I tried modifying the updated formula however, now I'm getting a #CIRCULAR REFERENCE error. I'm trying to sort it out, but maybe it has to do with changing from row to column?

    "and you will need to look for if [AMYR]:[AMYR] is not blank (versus your range of [AMYR]@row:[AMYR]@row)."

    Thanks!

  • Eric G
    Eric G ✭✭✭

    FYI @Genevieve P., I just re-entered the formula and now I'm getting an #INVALID OPERATION error. FYI, I have some values that are "0" if that matters.

  • Hi @Eric G

    My apologies, I was missing a Range reference in the formula above!

    =RANKEQ(AMYR@row, AMYR:AMYR) + COUNTIFS(AMYR:AMYR, <>"", [Row ID]:[Row ID], <=[Row ID]@row, AMYR:AMYR, RANKEQ(@cell, AMYR:AMYR) = RANKEQ(AMYR@row, AMYR:AMYR)) - 1

    This should be input in its own column, and then can be converted to a Column Formula.

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Eric G
    Eric G ✭✭✭

    Hi @Genevieve P., I used the updated formula and it worked. However, I'm back where I was in skipping some of the rankings. Here are the latest results I pasted into excel...

    Numbers 3, 6, and 7 were skipped.

  • Hi @Eric G

    Would you be able to post a screen capture with the formula "open" in Smartsheet, like I have above? Showing the AMYR column and your RowID column, but blocking out any other sensitive data.

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Eric G
    Eric G ✭✭✭

    @Genevieve P., yes of course! FYI, AMYR = Average MY x Ranking


  • Aha! Thank you, this helps!

    It looks like you're missing the third system auto-number helper column:

    • Column to Rank (Average MY x Ranking)
    • Auto-Number column (called...?)
    • Column with Formula (Average MY & Weight Ranking)

    In my formula I'm looking over at an auto-number column in the sheet, called "Row ID". Notice that the formula is placed in a separate column.

    =RANKEQ(AMYR@row, AMYR:AMYR) + COUNTIFS(AMYR:AMYR, <>"", [Row ID]:[Row ID], <=[Row ID]@row, AMYR:AMYR, RANKEQ(@cell, AMYR:AMYR) = RANKEQ(AMYR@row, AMYR:AMYR)) - 1

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Eric G
    Eric G ✭✭✭

    Hey @Genevieve P., I realized the formula being shown was the original one, and not the updated one. I entered the updated one as so...

    =RANKEQ([Average MY x Ranking]@row, [Average MY x Ranking]:[Average MY x Ranking]) + COUNTIFS([Average MY x Ranking]:[Average MY x Ranking], <>"", [Row ID]:[Row ID], <=[Row ID]@row, [Average MY x Ranking]:[Average MY x Ranking], RANKEQ(@cell, [Average MY x Ranking]:[Average MY x Ranking]) = RANKEQ([Average MY x Ranking]@row, [Average MY x Ranking]:[Average MY x Ranking])) - 1

    Good news is I got the result I wanted!

    Bad news is when I try saving the sheet I get the following error...

    😥

  • Hi @Eric G

    I'm glad we got the formula working! 🙂

    In regards to that error, have you been able to save the sheet? I would personally first go through a few troubleshooting steps (check network connection, try a different browser, save other sheets, etc). If that hasn't resolved the error, please do contact Support as the pop-up window suggests, with the sheet URL and a screen recording of the issue, including the steps you took leading up to this.

    Thanks!

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!