Is there a way to have a cumulative formula?

Tvl_97

Hey everyone,

I would like to know if there's a formula that calculates the cumulative time because I have to drag it down everytime.

I have 3 columns:

1) Task

2) Task Duration

3)Cumulative time

I know that for row 2, I can just do [Task Duration]@row + [Task Duration]1, but I can't do that for row 1.

Also, by using this method, if I remove a row in the middle (i.e. row 10), everything else after it will be #BLOCKED because the reference got deleted.

Is there another method of doing it?

Paul Newcome

Insert an auto-number column. Then insert a text/number column (called "Row" in this example) and insert this column formula:

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

Then in the cumulative column you would enter

=SUMIFS([Task Duration]:[Task Duration], Row:Row, @cell <= Row@row)

Paul Newcome

Insert an auto-number column. Then insert a text/number column (called "Row" in this example) and insert this column formula:

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

Then in the cumulative column you would enter

=SUMIFS([Task Duration]:[Task Duration], Row:Row, @cell <= Row@row)

Tvl_97

I'm trying to understand this method and I have a couple of questions:

Is it normal that the Auto-number column is empty?

What is the purpose of these new columns?

Where do I put this formula?

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

Is it in the new "Row" column?

When I put that formula in the "Row" column, all I get is 1 for every row (Maybe because the auto-number is empty?)

Paul Newcome

@Tvl_97 Once you save the sheet, the auto-number column should populate with a unique number on each row base on the order the rows were added.

Yes. The MATCH formula goes in the Row column and will output the row number once you save and the Auto-number column populates.

Tvl_97

@Paul Newcome

Thank you very much for your help!

Tvl_97

Hey @Paul Newcome,

I have another situation with the cumulative function

Instead of 3 columns, I have 5 which are:

1) Group

2) Task

3) Duration (per group)

4) Duration (per task)

5) Cumulative time

The group duration is the sum of all the task duration in that specific group:

=SUMIF(Group:Group, Group@row, [Task Duration]:[Task Duration])

I would like the cumulative to calculate the maximum value of the group duration.

So from rows 1 to 7, it would be 113

Then from rows 8 to 12, it would be 113+80 = 193

Then from rows 13 to 17, it would be 193 + 46 = 239

Do you think it is possible to modify the cumulative formula or is there a better way to do this?

Thank you

Paul Newcome

Why not just run cumulative on the [Task Duration] column?

Tvl_97

With the values in cumulative column, I will convert them into number of days by dividing the values by 480 (8 hours) and rounding up the result.

If I run the cumulative on [Task Duration], the groups that have a lot of tasks will be split into 2 days.

So for a group that has multiple tasks (None of the groups' duration will go over 480), I would like to fit them into 1 day.

Robert Way

I have another issue. The cumulative formula appears to be working as designed. But, I need the cumulative total to be at the the top of the column. I need to see the cumulative total for the most recent date at the top of the column. I have looked at how or if the LINE-ID column can be auto-numbered counting up, with no results. Even if that was possible how would the formula look?

Paul Newcome

@Robert Way Flip your [Row ID] argument in the SUMIFS to be greater than or equal to instead of less than or equal to. You can also switch it to evaluate the dates as opposed to the row.

Svitlana Iievlieva

@Paul Newcome

Hi Paul,

Hope you are doing well.

I have a question (I'm new with smartsheet), I've tried to recreate your solution that you provide here and for

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

got this

Could you advise, why it's happened?

Thanks,

Svitlana

Andrée Starå

Hi @Svitlana Iievlieva

I hope you're well and safe!

Try something like this. (there was a square bracket missing)

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

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Svitlana Iievlieva

Hi Andrée Starå!

Thank you!

It's help!

Where were my eyes? :) :)

Svitlana

Andrée Starå

@Svitlana Iievlieva

Excellent!

Happy to help!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!