Referencing a column from the top down to the current row - WITHOUT ABSOLUTE REFRENCE

I am trying to refer to a column from the #1 row down to the current row in a formula

For example say I have a column called Monthly Cost, I could have a formula

=SUM( [Monthly Cost]$1:[Monthly Cost]@row)

And that formula will very nicely give me a running subtotal line by line

Except for one issue - I want to make it a column formula - and Smartsheet does not like absolute references in column formulas

Smartsheet does not like

=SUM( [Monthly Cost]:[Monthly Cost]@row)

that is apparently a syntax error.

Any ideas on how to reference a column from row 1 to the current row without using an absolute cell refrence ?

Thanks

Find more posts tagged with

Accepted answers

Paul Newcome

You would insert two more columns.

System Generated Auto-number column with no special formatting.

Text/number column with the following column formula:

=MATCH([Auto-number]@row, [Auto-number]:[Auto-number], 0)

Then you would change your SUM to a SUMIFS and say to sum the Monthly Cost for all rows where the text/number is less than or equal to the text/number @row.

=SUMIFS([Monthly Cost]:[Monthly Cost], [Text/number]:[Text/number], @cell <= [Text/number]@row)

All comments

Paul Newcome

You would insert two more columns.

System Generated Auto-number column with no special formatting.

Text/number column with the following column formula:

=MATCH([Auto-number]@row, [Auto-number]:[Auto-number], 0)

Then you would change your SUM to a SUMIFS and say to sum the Monthly Cost for all rows where the text/number is less than or equal to the text/number @row.

=SUMIFS([Monthly Cost]:[Monthly Cost], [Text/number]:[Text/number], @cell <= [Text/number]@row)

David Dolch

That should work. Thank You.

I simplified the above example to just concentrate on the issue.

In reality, I am not using a simple SUM, I am using two COUNTIF statements nested within an other IF statement, but the concept should work. It is just going to be a long formula.

Thanks Again

Paul Newcome

Happy to help. 👍️

The COUNTIF statements should only need to change to COUNTIFS and then add a single range/criteria set to both to account for the text/number (row#) column.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.