# Is there a way to have a cumulative formula?

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:

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?

Tags:

• 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

thinkspi.com

• 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

thinkspi.com

• 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?)

• @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.

thinkspi.com

• Thank you very much for your help!

• Hey @Paul Newcome,

I have another situation with the cumulative function

Instead of 3 columns, I have 5 which are:

1) Group

3) Duration (per group)

5) Cumulative time

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

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

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

thinkspi.com

• 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.