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

haley brianne

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:

Can someone please assist? TIA!

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