RANKEQ with multiple filtering criteria

Drew Frazelle

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

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