Multiple conditions IFERROR statement with RANKEQ
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.
Answers
-
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")
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!