Ranking within hierarchy (=RANKIF)... using CHILDREN/PARENT/ANCESTOR instead of COUNTIFS

Julian Bergmann

Hi!

I want to create a ranking within a hierarchy, here is a attached a simplified example of what I'm trying to achieve (my real sheet is 200+ lines and has a 5-level hierarchy).

Right now I'm using this formula (in column Rank hierarchy 1) =IF(Hierarchy@row = 1;COUNTIFS(Hierarchy:Hierarchy; $Hierarchy@row; Value:Value; ">" + Value@row) + 1;"") and it is working just fine. Adding the same formula with additional conditions for columns Rank hierarchy 2 and 3 works as well.

However all these COUNTIFS are really bad for the sheet performance. So I'm wondering if it is somehow possible to make use of Smartsheet's CHILDREN / PARENT / ANCESTOR instead?

Looking forward to your ideas - Thanks!

-Julian

Jenn Hilber

Hi @Julian Bergmann ,

I just had this same challenge. You can use the RANKEQ formula with a COLLECT formula in place of the range.

=RANKEQ( number, COLLECT( range, criterion_range1, criterion1, [ criterion_range2, ​criterion2​... ]), [order])

Number = value

COLLECT = the range with criteria added, try COLLECT(Value:Value, Hierarchy:Hierarchy; $Hierarchy@row)

[order] = 0 for descending or 1 for ascending

I had to add a helper column to rank each parent group to make it work within the hierarchy. View the formulas here: https://publish.smartsheet.com/14547fa9f74244a69b86318651b20cc1

Matt Johnson

Hi @Julian Bergmann

I think this is the formula you are looking for. Placing this formula in the Hierarchy column should do the trick.

=COUNT(ANCESTORS([Category]@row), 1)

Julian Bergmann

Hi Matt, and thanks for your reply!

Unfortunately this doesn't really help, as I'm not trying to count the Ancestors, but ranking the Children within each Parent. Does this makes sense? I think the screenshot above explains it a bit better.

I'm only using the Countif formula as a workaround because there is no Rankif in Smartsheet.

-Julian

Jenn Hilber

Hi @Julian Bergmann ,

I just had this same challenge. You can use the RANKEQ formula with a COLLECT formula in place of the range.

=RANKEQ( number, COLLECT( range, criterion_range1, criterion1, [ criterion_range2, ​criterion2​... ]), [order])

Number = value

COLLECT = the range with criteria added, try COLLECT(Value:Value, Hierarchy:Hierarchy; $Hierarchy@row)

[order] = 0 for descending or 1 for ascending

I had to add a helper column to rank each parent group to make it work within the hierarchy. View the formulas here: https://publish.smartsheet.com/14547fa9f74244a69b86318651b20cc1

Julian Bergmann

Hi @Jenn Hilber,

wow, so simple and yet powerful :-)

This makes the sheet much easier to maintain, and the performance is a lot better now too.

Thank you so much for this great solution!