nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

Welcome!

This Post is Archived and Read-Only

The posts in this section are historical and no longer monitored for accuracy. If this discussion interests you and you'd like to join in, please visit the active Community to post and engage.

Browse Active Topics

SUMIF children when date from another column is older than TODAY()

csprocket777

I'm trying to get the numeric sum of the children of a parent when the date from another column is older than today.

I get #UNPARSEABLE when I try.

This is what I have:

Screen Shot 2017-10-24 at 4.14.30 PM.png

Screen Shot 2017-10-24 at 4.14.42 PM.png

Comments

steven.reed14881

csprocket777,

I have figured out a solution to your issue, but it involves adding one more column. I don't know whether you can achieve the desired result by modifying your formula, but ultimately it becomes simpler by breaking it into a two step process, as follows:

Add the new column which determines whether the date is prior to today, and if so, displays the Column3 value. If not, the result is 0. This is achieved through this formula: =IF([Column2]2 < TODAY(), [Column3]2, 0)
Then you can derive the sum of those that are earlier than today with the following formula: =SUM(CHILDREN()).

I am including a screenshot of the whole thing. Let me know if you have question.

Steve

csprocket777

So this is a formula that goes in each child cell? I was hoping to operate entirely in the parent row.

Let me try this...

csprocket777

This works. It would be ideal if I didn't have to copy the formula into each new row as rows are created and that it work in the parent row exclusively.

csprocket777

I get closer when I do something like this:

=SUM(COLLECT([Column3]:[Column3], [Column2]:[Column2], <TODAY()))

Tim Fung

Hi csprocket777,

your original formula is wrong

=sumif(children([column2](your row number), <today(),children())

you were missing the row number to indicate the what is the parent cell

hope it can help

Sign in to join the Community!

Quick Links

Find more posts tagged with

Formulas