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:
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?
Best Answer
-
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)
Answers
-
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)
-
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.
-
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
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
-
Why not just run cumulative on the [Task Duration] column?
-
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.
-
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?
-
@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.
-
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
-
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.
-
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!