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
Best Answer
-
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)
Answers
-
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)
-
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
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.8K Get Help
- 65 Global Discussions
- 69 Industry Talk
- 385 Announcements
- 3.6K Ideas & Feature Requests
- 56 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!