# Is there a way to have a cumulative formula?

Options
✭✭
edited 05/12/22

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:

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭
Options

Thank you very much for your help!

• ✭✭
edited 05/19/22
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭
edited 05/19/22
Options

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.

• Options

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?

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Options

Hi Andrée Starå!

Thank you!

It's help!

Where were my eyes? :) :)

Svitlana

• ✭✭✭✭✭✭
Options

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!