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.
Sign in to join the conversation:
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
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])
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!
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.
Oh, that's incredible! You totally just made my day.
Thanks, again!
Excellent! Happy to help!
The dashboard previously contained 16 widgets, and I have confirmed that the widgets still exist in the backend. However, when I open the dashboard, nothing is displayed, and I am unable to enter Edit Mode or access the widgets/data sources. I have already confirmed the following: I am the owner of the dashboard and have…
I have an end date column and want to add 30 days to that for a new column that shows due date. How do I add 30 days to a date automatically?
Why is it so convoluted now! I eventually figured out the changes and ended up with a sheet that Smartsheet completely reorganised the rows and the sheet is now useless to me as it is not in a logical order for me to easily restore to its original layout. This is not an "improvement"! It was far easier to upload data…
