Can a range be specified with a beginning and no end?

Jacob Slaathaug
edited 12/09/19 in Smartsheet Basics

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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    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.

  • 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. 

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    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.

  • Hi Jacob and Howard, 

    Give this a try:

    =COUNT(DESCENDANTS([PARENT Name]1))

    This will count all indented parents and children.

    Best,

    Kara

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    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.

  • 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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    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.