How to get top 5 most caused services from countifs roll-up by month

I work for a bath remodeling company and we want to track the top 5 reasons we have services from our service calendar in Smartsheet. I created a COUNTIFS roll-up formula that is below and located in the children rows below Top # 1 Service from pic:

=COUNTIFS({Nature}, $Label@row, {Month}, INDEX(ANCESTORS(), 1), {year}, INDEX(ANCESTORS($Label@row), 1)) + COUNTIFS({COMPLETED-nature}, $Label@row, {COMPLETED-month}, INDEX(ANCESTORS(), 1), {COMPLETED-years}, INDEX(ANCESTORS($Label@row), 1))

I've seen things on here for the Large function or the Rankeq function but when I include a formula for either on the parent row, if gives me a circular reference or a blocked reference for the cell and children. I would like the parent row to include the Top # 1 service from the label row and the number from the roll-up next to it (ex. Silicone 52). Once I get this right, I was going to copy and paste the whole thing to find the Top # 2 service and so on. We want this data by month so we can track trends and other metrics in one sheet.


Best Answer

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 01/07/22 Answer ✓

    It's your ancestors reference that is causing the issue. Change them to an absolute reference to the first cell and it will work.



    turns into



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!