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



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!



Best Answer