RANKEQ with multiple filtering criteria

I have been trying to rank my rows that are listed as "Tier 1" in the {Level} column in order of importance based on the following criteria:
1) Highest Rank: Rows with the oldest {R/O Create Date} AND BOTH {OOS} and {Warranty} columns are checked.
2) Second: Again, oldest R/O create date, with the {Warranty} column checked.

3) Third: Oldest R/O create date, with {OOS} column checked.

4) Fourth: Oldest R/O Create Date.

Maybe I am trying to filter everything down against too many variables. Currently, we have rows listed as Tier 3, and Tier 2 {Level} as well, but I do not want to rank those, as those rows are already in production so to speak. I do have a helper column {Age} with this formula (=TODAY() - [R/O Create Date]@row) if that is helpful.

Thank you SmartSheet Wizards!!!

~ Drew

Find more posts tagged with

Formulas

collect

ranks

IF(AND

Accepted answers

All comments

Paul Newcome

Are you able to provide some screenshots for context?

Drew Frazelle

Paul,

Totally, please see attached.

Thank you for taking a look. Currently, the Order column is where I would like this new formula as it currently does not rank all rows.

~ Drew

Paul Newcome

My recommendation would be a nested IF statement to output the appropriate RANKEQ formula on a row by row basis (plus the total number of rows in higher priority groups).

For example…

=IF(AND(Warranty@row = 1, OOS@row = 1), RANKEQ(both are checked), IF(Warranty@row = 1, RANKEQ(warranty is checked) + COUNTIFS(both are checked), IF(OSS@row = 1, RANKEQ(oss is checked) + COUNTIFS(both are checked) + COUNTIFS(warranty is checked and oss is unchecked), RANKEQ(neither is checked) + COUNTIFS(warranty is checked) + COUNTIFS(oss is checked))))

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.