Filter only children row when certain criteria is met.

Hello Community,

What I am trying to do here is to display the milestones achieved by each client. The milestones are contained in children rows. The way to identify those milestones would be displaying the row with the highest date within its own family group and box check checked.

Once I am able to filter the rows that meet such criteria, I will display them in a dashboard using a widget chart

So far I am getting #UNPARSEABLE errors with all I have tried;

=COUNTIFS(CHILDREN([Complete?]@row), =1, & ([Date]@row),= ISDATE())

Thanks in advance for your help to this newbie.

Charlie

Find more posts tagged with

Sheets

Formulas

Dashboards

functionality

Workflows in Smartsheet

Accepted answers

All comments

Jeff Reisman

The COUNTIFS syntax is COUNTIFS(Range 1, Criteria 1, Range 2, Criteria 2...)

For COUNTIFS to count a row, all the included criteria must be true for a row. The below will count all child rows of the row the formula is on, where the Complete? checkbox is checked, and where the Date cell contains a date value.

Try this:

=COUNTIFS(CHILDREN([Complete?]@row), 1, CHILDREN([Date]@row), ISDATE(@cell))

COUNTIFS Function | Smartsheet Learning Center

Create Efficient Formulas with @cell and @row | Smartsheet Learning Center

Jeff Reisman

You initial question was kind of a mix between counting and just having a filtered view. For a filtered view with your criteria, you can do something like this:

Charlie T.

Hi Jeff Reisman,

Thank you very much for your answer. It has helped me to understand this particular formula better. As I mentioned in my initial question, I aim is to display that children row where this criterion is met(last milestone achieved by group). My screenshot shows that my two parents are: UKPOC and Aberdeen Ave, whose current milestones and previous dates updated are "BOQ submitted" and "Hardware Received", respectively. Your suggested formula sitting in the parent row indicates the intended row counting down. However, how can I filter these rows, so these are the only ones visible in my report/dashboard?

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.