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

Jacob Slaathaug · 11/29/18

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)))

Andrée Starå · 11/29/18

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

ahoward · 06/04/19

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å · 06/05/19

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, andree@getdone.se)

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Kara Lumley · 07/02/19

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å · 07/03/19

Hi Kara,

Thanks!

This new function makes many scenarios so much easier to set up.

Best,

Andrée

Kara Lumley · 07/05/19

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å · 07/06/19

Hi Kara,

Thanks for letting us know!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Grace Qin · 06/01/23

I found a workaround.

Here is a trick to understand before writing the formula:

Even though Smartsheet cannot do [Column Name]3 to [Column Name]Infinite, but it can do:

[Column Name]3 to [Column Name]@row
Set the range with a selection like the picture below, and the range changes dynamically as you insert rows in between.

Now, if you want to count from [Column Name]3 to [Column Name]Infinite, you can:

=COUNT( [Column Name]:[Column Name])-COUNT([Column Name]1:[Column Name]3)

Hope it helps.

Best regards,

Grace

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

Comments

Categories