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 systemgenerated autonumber 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

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!

FYI @Genevieve P., I just reentered 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

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.

@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 autonumber helper column:
 Column to Rank (Average MY x Ranking)
 AutoNumber column (called...?)
 Column with Formula (Average MY & Weight Ranking)
In my formula I'm looking over at an autonumber 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

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 popup window suggests, with the sheet URL and a screen recording of the issue, including the steps you took leading up to this.
Thanks!
Genevieve
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 62 Global Discussions
 46 Industry Talk
 386 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!