How do you reference the cell where the formula resides to have a cumulative formula. @cell does not

I need to have a cumulative sum of hours spent on a task. The user can enter hours multiple times, daily, weekly and the Total Logged Hours will add them up in a cumulative sum. So you want a column formula like = [Logged Hours]@row + @cell where @ cell is the Total Logged Hours cell and Logged Hours is the cell where the user enters the hours spent on the task that day/week.

I had the AI generate the formula, just to check myself and it gave me:

=[Log Work (hours)]@row + @cell

which is an #INVALID OPERATION.

I also Googled this example which I tried and gives you INVALID OPERATION:

"In Smartsheet, to use the content of the cell that currently has the formula within the formula itself, you can use the special "@" symbol, which refers to the current cell's value within a formula; for example, if you want to add 5 to the value of the current cell, you would write the formula "=@cell + 5" in that cell. "

This doesn't work. I get the same thing #INVALID OPERATION.

Find more posts tagged with

Accepted answers

All comments

KPH

The problem

@cell does not do that. It is used within another function to evaluated each cell within a range. For example, as part of a COUNTIF or SUMIF function. If you had data like this:

You could use @cell in a SUMIF function like this,

=SUMIF(Date:Date, MONTH(@cell ) = 2, [Hours logged]:[Hours logged])

This will look at the Data column and if the Month of any cell in that column is 2, the value from the Hours logged column will be included in the sum.

You cannot use @cell alone, it needs to be in a function that includes a range to evaluate.

Onto your question

Possible question 1

If you have a column called Log Work (hours) and want to add up all the values in that column the formula is

=SUM([Log Work (hours)]:[Log Work (hours)])

Possible question 2

If you want to have a running total next to each row, like this:

Option 1

Then you could put this formula in row 2:

=[Cumulative hours]1 + [Log Work (hours)]@row

This would take the cumulative hours from the row above and add it to the Log Work (hours) in the current row.

If you drag that formula down the 1 will change to a 2, 3, 4, etc for each row so you are always referencing the cell above.

Row 1 would need to be slightly different as there is no running total to add to it:

=[Log Work (hours)]@row

Option 2

Another, more elegant, method would be to assign a row number to each row and use SUMIF to add up all the hours on the rows where the row number is the same or less than the current row.

The formula is:

=SUMIF(Row:Row, <=Row@row, [Log Work (hours)]:[Log Work (hours)])

The advantage here is that the same formula can be used on each row and it can be converted to a column formula.

The disadvantage is that you will need to create the row number column.

How to add the row numbers

You can number each row easily by adding using auto number column (insert column type "Autonumber"). However, if you insert a row between existing rows, the new row number will be out of sequence, which will impact the cumulative hours formula.

The work around is to create the auto number and then create a second column (I call mine Row) to find the row number of the row with the current autonumber in it, using a MATCH function.

=MATCH(Autonumber@row, Autonumber:Autonumber, 0)

Hope this helps.

If you copy these formula, ensure you adjust the column names to match the column names you use.

Oana A.

Thank you very much for your excellent suggestions. I knew about all these options involving multiple rows, but none of them gives me what I need because I just wanted to do ThisCell = ThisCell + AddedHours on a single row for 1 task. Unfortunately Smartsheet cannot do it. @cell or @thiscell should be used to reference the contents of the cell where the formula resides for a cummulati8ve answer. Much more efficient than adding rows and columns to achieve something so simple.

KPH

You cannot create a formula that takes the value in a cell adds another number to it and returns the sum within the same cell. Either the formula would be deleted by the data being entered or it would be constantly trying to update itself as it recalculates. You do need one of the numbers to be in a different cell then you can add two things together. You could achieve the look you want by using an automation to update a cell with the output of a formula, but that formula would still need two columns of things to add together.

Moughabat

@KPH - your suggestion below assumes that we can have formulas in actions of an automation which isnt the case…. i hope i am wrong…

You could achieve the look you want by using an automation to update a cell with the output of a formula, but that formula would still need two columns of things to add together.

@Oana A. Any luck on your requirement? I'm kind of facing same issue.

Oana A.

I already talked to Smartsheet multiple times and they do not have a solution for cumulative formulas at this time, just like they do not have an automation that copies one cell to another. Therefore the scenario where you have the user always enter a number of logged hours in cell A, automatically copy that into a cell B that gets added to cell C, is not possible. This is what I need and they don't have it.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.