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.

Find more posts tagged with

Formulas

Accepted answers

L_123

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

All comments

L_123

try pasting the formula below into a parent cell

=large(children(),2)

Does this return a circular reference?

Justin Ramos

@L_123 I just tried that formula and the parent row came up #BLOCKED and the children came up #CIRCULAR REFERENCE

L_123

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

Justin Ramos

@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

Justin Ramos

@L_123 Actually nevermind, I was able to create an index match formula to do it what I needed it to do. Thanks again!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.