Sumproduct for Ranking

Eric G

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!

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