Formula help sumif has no children

Geordie

Hello,

I want to sum up every number in a column only if the row has no children. I'm sure there's an easy way to do it, but I've bashed my head up against it and can't figure it out.

Many thanks!

Geordie

Paul Newcome

You will need to use a helper column to mark in some way which rows do not have any children and then sum based on that.

 

My personal suggestion would be a text/number type column with a formula such as this...

 

=IF(COUNT(CHILDREN([Sum Column Name]@row)) = 0, [Sum Column Name]@row)

.

This will essentially duplicate the column you are wanting to sum but only in rows that do not have children. You can then sum this entire helper column since parent rows are left blank.

 

=SUM([Helper Column]:[Helper Column])

Geordie

Thanks for your help Paul!

Is there really no way to do it without adding an extra column? I was definitely trying to avoid this.

My goal is to make templates that team members can use without having to deal with the guts of the sheet. I realize that I can add an extra column and then hide it, but the problem with that is then if anyone adds an extra row to the sheet, the formula in the hidden column won't be duplicated to the new row. Then anyone using the sheet needs to know how to unhide columns and copy formulae, and more problematic, needs to remember to do it every time. And I definitely don't want to be relying on any information generated by a process that's so very susceptible to human error!

Can you explain to me why something like this doesn't work:

=sumif([column_to_sum]:[column_to_sum], count(children(@CELL))=0)

This just seems like a fairly standard thing that users would want to do, isn't it? So I guess I'm having a hard time accepting that it's simply not possible.

The only other way around this would be if there were some way to automatically add a formula in a column when new rows are created. If there is, then that's actually even better. But if not, I'm kind of back at square one.

Any additional help is much appreciated!

Geordie

Paul Newcome

I cannot explain WHY 

 

CHILDREN(@cell)

 

does not work. I just know that it doesn't.

 

As far as adding new rows and having the new formula auto-populate goes...

 

As long as either the two rows above or the two rows below the new row have the formula in it, it will auto-fill as soon as text is entered in the primary column of that row.

 

For example...

You have rows 1 - 10 populated with the formula in all of them. If you insert a new row at the top of the sheet and enter a new task there, since rows 2 and 3 (formerly 1 and 2) both have the formula, it will auto-populate in the new Row 1 as soon as the Primary column has something in it.

 

If you add a new row at the bottom of the list, you would need to use row 11, but since 9 and 10 already have the formula, row 11 will automatically have the formula in it as soon as the primary column is populated.

 

Inserting a row into the middle will work the same way. Since either the 2 rows above or the 2 rows below have the formula, it will auto-populate as soon as the primary column is populated.

 

You can even add multiple rows right in the middle of the sheet. As long as the primary column is filled in either from top to bottom or bottom to top in that new section, the formula will pull from the already populated rows.

.

I hope that helps.

Geordie

Oh, that's incredible! You totally just made my day.

Thanks, again!

Paul Newcome

Excellent! Happy to help!