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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
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.
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
@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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 321 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 277 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!