Can someone assist with looking at children rows vs selecting exact cells?

Hello!

Each time I change the sorting of this sheet, my formulas are changed. The formula is technically correct, the issue is that I am referencing exact cells, so when sorting it throws it off.

=SUMIF([Open By Year]38:[Open By Year]43, @cell < TODAY(), [Total Units Signed For]38:[Total Units Signed For]43) - [# Open]@row

I have attached a screenshot below:

Screenshot 2023-03-08 at 11.51.44 AM.png

Can someone please assist? TIA!

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

Paul Newcome

You would replace each of your ranges with a CHILDREN function.

CHILDREN([Column Name]@row)

haley brianne

Thanks, @Paul Newcome! Sadly, I am still lost.. can you elaborate more?

I have tried many other ways.

This is the current one I have: =SUMIF(CHILDREN([Open By Year]@row) < TODAY(), CHILDREN([Total Units Signed For]@row) - [# Open]@row)

Paul Newcome

It looks like you have changed the overall syntax. Try the first formula but only changing the ranges to be CHILDREN([Column name]@row).

=SUMIF(CHILDREN([Open By Year]@row), @cell < TODAY(), CHILDREN([Total Units Signed For]@row)) - [# Open]@row

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.