Can a range be specified with a beginning and no end?
I'd like to create summary rows at the top of my sheet, but specifying the entire column as a range in the formula is causing #circular reference and #block errors. Is there a way to specify a range from row 25 to infinity? I'd like additional rows to automatically be included in my summary calculations.
The formulas I'm using are similar to what is shown below.
=COUNTIFS([Cond. Removed]:[Cond. Removed], =1, Parent:Parent, ="Retirement", [Pole Top Assembly]:[Pole Top Assembly], NOT(ISBLANK(@cell)))
Comments
-
Hi Jacob,
Would it work to have the first row as a Parent and then SUM all children?
I hope this helps you!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
0 -
I had the same question, no perfect solution.
I tried to sum all children as you suggested, but it only gets direct descendants of the parent row. My sheet has an arbitrary number of indents (and indented indents) so it's not practical to sum each and everyone one of those all the way to the top parent.
I'm trying this as a workaround: create a row labeled "Insert above this row!" and put it at the bottom. If you sum the range from the first row through that row, then if you add new rows, it updates the formula to match the row number of that row.
0 -
Hi Howard,
There might be a better solution to your scenario.
Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
0 -
Hi Jacob and Howard,
Give this a try:
=COUNT(DESCENDANTS([PARENT Name]1))
This will count all indented parents and children.
Best,
Kara
0 -
Hi Kara,
Thanks!
This new function makes many scenarios so much easier to set up.
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
0 -
Hi Andre and Howard,
FYI, there is an issue with the new descendants formula. We have a fix for it and should know early next week when it will be released.
Thanks,
Kara
0 -
Hi Kara,
Thanks for letting us know!
Have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
0