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!

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

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

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

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.

Genevieve P.

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

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.

Genevieve P.

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.

Eric G

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

Genevieve P.

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

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...

😥

Genevieve P.

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.