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

Find more posts tagged with

Sales

Manufacturing

Non-profit

Construction

Sheets

Finance

Services

Marketing

Support

IT & Operations

Product Development

Project Management

Comments

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

ahoward

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å

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

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å

Hi Kara,

Thanks!

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

Best,

Andrée

Kara Lumley

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å

Hi Kara,

Thanks for letting us know!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Grace Qin

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

Sign in to join the conversation!

Quick Links

Trending discussions

Sheet -Ganhtt Chart
Is it possible to display both the planned and actual duration for a single task on a Smartsheet Gantt chart? I would like the Gantt bar to show the planned duration (e.g., in blue) based on a Planned End Date column, and then show the actual duration (e.g., in green) based on an Actual End Date column. Can the Gantt view…
Color-Coded Tags in Board View
I noticed in the Board View tags are grey by default but in the how to videos, in the Board View some of the tags are colorful. I'd like to leverage that same functionality as I'm building a research repository with many different types of research in it and a visual cue would be lovely. Can anyone assist? I attached a…
Monthly feedback from multiple individuals
Hello community, I need help figuring out how to set up a sheet to collect monthly comments on several projects. Each project is assigned to a different person who need to provide monthly feedback on its progress. Ideally, I would like them to receive a reminder notification on the report due date, followed by reminders if…