Formula to always Summarize all 5 cells to the right of a column, including new columns added.

A Rose

Hi,

Is there any formula that can return the summary of any of the 5 columns to the right of this column layout, no matter what columns are inserted from there on?

I keep on adding columns, but I want it to always calculate the columns that are now (or in the future) within 5 columns to the right.

Thank you,

Mike TV

@A Rose

This is one of those situations where you need to reevaluate your sheet design and how you're using it. When you require something to be done a very specific way and SmartSheet isn't designed to do that or doesn't have a formula created to do that, then you need to step back and re-think how you want your sheet to work with SmartSheet limitations in mind and design a working version doing things differently.

A Rose

That is true Mike!

For anyone with the same issue:

I might start clearing old columns with the clear cell value automation and re-use those same columns.

Thanks,

Mike TV

@A Rose

Are you saying that if you insert 5 columns into your sheet you want a formula that will count the 5 new columns or to keep counting the same 5 columns that were counted before that are just in a new position?

If it's to keep counting the same 5 columns always, then you need absolute references. If it's to count an entirely new set of 5 columns after new columns are inserted, then I'm not sure how to do that.

A Rose

Hi @Mike TV,

I need the 2nd one, to always count any column to the right (a total of 5 columns to the right) even new columns,

Thanks,

Colleen Patterson

@A Rose Are you wanting this to return the data results collected from those columns? Or is there math being done? Can you send a screenshot of your example?

A Rose

Hi @Colleen Patterson,

Here you go

Formula:

=SUM([Column1]@row:[Column5]@row)

A Rose

When inserting a Column after Column: "Formula" named: "Column0" the formula should count that column as #1 through 4,

Meaning Column0:Column4

Colleen Patterson

@A Rose

So there is not a good way to do that that I know of when the columns are changing, but a workaround could be:

1. Create 2 hidden columns to use a bookends to your desired summary columns. In your example above, place them between 1-Formula and Col1, and 2-after Col5
2. =SUM([Book 1]@row:[Book 2]@row) this would return a sum of all values inside your bookends
3. When you create new rows in your section, move your second bookend to the new desired place.

Although not perfect, it would minimize your need to update the formulas, and with one column move, you should always be good to go.

*Note: this will mean that the returned data will change, as the columns are added, so a SUM that used to =100, may now return a different value once more columns are added. Not sure if that would matter.

If anyone else has a different / better suggestion, please tag me in the response as well.

A Rose

Hi,

I need to be able to add multiple columns frequently enough, 2 helper columns wouldn't help in my situation,

I assumed there's no such formula, as formulas are mainly or always work with the actual column name and not with the range of columns, tried my luck.

Thanks,

Mike TV

@A Rose

This is one of those situations where you need to reevaluate your sheet design and how you're using it. When you require something to be done a very specific way and SmartSheet isn't designed to do that or doesn't have a formula created to do that, then you need to step back and re-think how you want your sheet to work with SmartSheet limitations in mind and design a working version doing things differently.

A Rose

That is true Mike!

For anyone with the same issue:

I might start clearing old columns with the clear cell value automation and re-use those same columns.

Thanks,