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
-
It's your ancestors reference that is causing the issue. Change them to an absolute reference to the first cell and it will work.
Edit:
INDEX(ANCESTORS(), 1)
turns into
currentColumn$1
Answers
-
try pasting the formula below into a parent cell
=large(children(),2)
Does this return a circular reference?
-
@L_123 I just tried that formula and the parent row came up #BLOCKED and the children came up #CIRCULAR REFERENCE
-
It's your ancestors reference that is causing the issue. Change them to an absolute reference to the first cell and it will work.
Edit:
INDEX(ANCESTORS(), 1)
turns into
currentColumn$1
-
@L_123 Thank you that worked! Do you have a recommendation on how to take the number that shows up and join it with the label that is associated from the primary column? Like an if then join? not sure how I would put that together
-
@L_123 Actually nevermind, I was able to create an index match formula to do it what I needed it to do. Thanks again!
Help Article Resources
Categories
Check out the Formula Handbook template!