Multiple conditions IFERROR statement with RANKEQ

summetg

Hi all,

I'm trying to make a modification to an IFERROR statement which is the following:

=IFERROR(RANKEQ([Weighted Project Score]@row, [Weighted Project Score]:[Weighted Project Score], 0), "Not Ranked")

This essentially runs a RANKEQ on all the entries, but if there's a score of 0 it returns value of "Not Ranked".

I am trying to also add as an "Or" condition, where if another column of "Current Status" is equal one of the following values - "Submitted", "Intake In-Progress" and "Assessment Completed" (there are few others) that we are returned the value of "not ranked" and still is not part of the overall RANKEQ ranking calc. I tried to modify it into something like this if the status is "Submitted" as start which didn't work:

=IFERROR(IFERROR(RANKEQ([Weighted Project Score]@row, 

[Weighted Project Score]:[Weighted Project Score], 0),

[Current Status]@row = "Submitted", "Not Ranked"))

I found a thread on Nested IFERRORs (Nested IFERROR — Smartsheet Community), but this isn't totally what would be my solution. Can anyone suggest anything.

Devin Lee

Hey @summetg

Use COLLECT to get just the data that has "Submitted" in the Current Status column.

=IFERROR(RANKEQ([Weighted Project Score]@row, COLLECT([Weighted Project Score]:[Weighted Project Score], [Current Status]:[Current Status], ="Submitted"), 0), "Not Ranked")

summetg

Hi Devin,

I did not think about the collect function! I think I'm close - how do I add other status that would impact getting the data. In addition to Submitted, I have "Assessment completed" and "Intake In-Progress".

I may have about 5-6 statuses I want to be included in the logic