Accumulated Value Column

Options

Hi,

Has anyone faced the need to use an accumulated value column?

This has been around for years for those who need to programm an accumulation of historical values in other apps and I have done it using multiple rows to record individual changes then another column to record the accumulation. Nothing super fancy really formula wise.

This becomes challenging in Smartsheet if we do not want to use multiple rows but just ONE attemting to keep record in a separate column of the accumulated value of a cell that is changing randomly its value.

Have you solved this before?

I could not find any formula nor trick to make it work yet.

I will welcome your suggestions or ideas.

Thanks

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hey @Cruz Ortiz

    If you're on an Enterprise plan, you can use Work Insights from the side panel to see calculations across time on a specific column. Here's more information: Use Work Insights to visualize your data

    Cheers,

    Genevieve

  • Cruz Ortiz
    Cruz Ortiz ✭✭✭✭
    Options

    Hello Good day,

    Thank you Genevieve.

    Interesting. Unfortunatelly I do not have Work Insights feature. I will look into it now but it may not be easy to try on unless we purchase it.

    If anyone else had an alternative suggestion suitable to a Bussiness plan license, please share.

    Thanks again.

    Cruz



  • Cruz Ortiz
    Cruz Ortiz ✭✭✭✭
    Options

    Ok, now I have Work Insights and unfortunatelly it does not do the job.

    Please

    see attached, you will see the "Days under Reporter" column will be changing when the ticket goes to the reporter (randomly ticked) and counts the days since BUT the Acum column is wanted/expected to accumulate all those days in one single row but so far I cannot achieve.

    Any thoughts how to solve?

    Thanks

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Cruz Ortiz

    Do you want your Accumulated Column to Sum or add together all the values in the "Days Under Reporter" column for a Total?

    If so, you can use the SUM function to do this:

    =SUM([Days Under Reporter]:[Days Under Reporter])

  • Cruz Ortiz
    Cruz Ortiz ✭✭✭✭
    Options

    Hi Genevieve

    Not that simple.

    I want the Acum ROW cell to add the total days count of "Days under reporter" when the ticket goes back to the reporter (randomly). When ticket goes back to the reporter the "Days under Reporter" count restart a new return days count.

    I hope that makes sense.

    Cheers,

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hey @Cruz Ortiz

    Thank you for explaining further, I understand now. There currently isn't a way for a formula to read a cell's historical data, it can only look at the display value being surfaced in each cell of that row. This means that if your Days Under Reporter cell changes back to 0, the Accumulated Column will see 0, not the previous data.

    The Work Insights panel does access cell history to display information, but it does not show this on a row-by-row basis in the grid of the sheet itself and requires specific columns to look for data in, as you noted.

    Depending on when the original clock starts for the counter, you could use a Record a Date workflow to record the first date that the initial clock started, then use your formula to look at that static date instead, if that helps.

    Please also add your vote and voice to these Product enhancement ideas:

    Cheers,

    Genevieve

  • Cruz Ortiz
    Cruz Ortiz ✭✭✭✭
    Options

    Hi Genevieve

    I thank you for your feedback.

    Definitely something Developers should consider.

    I will solve it for now with a hep sheet to copy over the row every time the Go back to reporter is triggered, then add up all the same ID rows. I guess there is no other way for now.

    Cheers,

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!