Sheet Summary formula to included newly added rows

laurareid

Hi,

I created my sheet with some formulas at the top before I created Sheet Summary.

Each day when new rows are added I am having to change the last number eg. 3861 to 3962. Is there a way to change the formula so the new rows are always included in the formula ?

Current Formula:

=SUMIF(Status19:Status3861, "Sent to Summit - Plumber", [Total Cost]19:[Total Cost]3861)

Thanks in advance

Yvonne Smythe

Hi @laurareid -

example - [Total Cost]:[Total Cost]

Removing the number range and leaving column name as above keeps it automatically updating.

Try this I hope it serves as a good point of reference:

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

laurareid

HI Yvonne,

My data starts at row 19 as I have reporting formulas in the first 19 rows. I plan to move my formulas to sheet summary but wondered if a formula could be used until I do this.

Thanks

Yvonne Smythe

https://community.smartsheet.com/discussion/comment/462534#Comment_462534

Have you tried just opening up the '3861' and setting it to a higher number inside the sheet limitation range? ie '8000' @laurareid

laurareid

Yes , unfortunately I receive #UNPARSEABLE

Yvonne Smythe

@laurareid - Try this:

=SUMIF(Status@row, "Sent to Summit - Plumber", [Total Cost]@row)

Then in the cell at row 19 turn the formula into a column formula. You should notice above row 19 there is no formula.

laurareid

Thank you, I will try that

Jennifer Kurtz

Hi @laurareid -

Perhaps there's an existing column you could reference to identify your formula rows & exclude them from the sum. Is there a Status selected for your formula rows? As long as it's not "Sent to Summit - Plumber," you could use the status as your condition, and reference the full columns as Yvonne suggested - e.g.,

=SUMIF(Status:Status, "Sent to Summit - Plumber", [Total Cost]:[Total Cost]

(If referencing the STatus column doesn't work as your condition, you could add a helper column to mark your formula rows.)

Does that help?

Yvonne Smythe

@laurareid

Forgot to send these 🙂

Paul Newcome

There is no way to properly do this without either inserting helper columns to move the formulas out of the data columns or going ahead and moving things into sheet summary fields.