Need to sum rows above current row - only if condition is met

Options

I need to get a running total for the "Standard Time (Hrs)" column for rows where "Filter" is not = 0.

The below formula gets a total for all the rows. However, I need each row to show the total for all rows above it. (As seen on the right in blue markup).

=SUMIF(Filter:Filter, <>0, [Standard Time (Hrs)]:[Standard Time (Hrs)])

Any ideas?

The alternative would be to write a script and update each row using the API but that seems excessive for something seemingly simple.

• ✭✭✭✭✭✭
edited 11/23/22
Options

Try the below setup.

1. "LINE-ID" : Auto Number Column (unless you have one already)
2. "ROW#" : Column Formula: "=MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)

=SUMIFS([Standard Time (Hrs)]:[Standard Time (Hrs)], Filter:Filter, @cell <> 0, [ROW#]:[ROW#], @cell < =[ROW#]@row)

• Options

The rows are sometimes moved for prioritization. If they were consistently in the order they were added in this would work. I think the moving of rows would cause this to break.

• ✭✭✭✭✭✭
Options

The way this is designed the ROW# constantly changes as you update it

• Options

The formula works on a single cell, but throws this error when it is applied as a column formula. No idea how it is looking at that many cells.. ~2000 rows, only about 50 meet the SUMIFS criteria..

• ✭✭✭✭✭✭
Options

Unfortunately, this formula is very limited to the size of the sheet (I have had this problem as well and because of that restructured many sheets to avoid using it).

Because it is running a MATCH to create the ROW#, every cell that has the SUMIFS is essentially running the MATCH formula for every row again, which each time is evaluating the LINE-ID column, it grows exponentially (so you end up reaching that 25,000,000 limit) regardless if the SUMIFS (or any other criteria for that matter) is met.

In your case it is probably a combination of the other columns as well, because just the 2000 rows should not be an issue but the [Standard Time (Hrs)] and [Filter] will also add to it (specifically if they are referencing and evaluating other columns)

• Options

I will just use the API to update the Hours Ahead column using python. Thanks for the help. I am not sure if you work for smartsheet, but if you do - it may be worth looking into refactoring the code to avoid this issue.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!