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

Justin Wade

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.

Leibel S

@Justin Wade

Try the below setup.

Add the below 2 columns:

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

then your hours ahead formula would be:

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

Justin Wade

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.

Leibel S

@Justin Wade

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

Justin Wade

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

Leibel S

@Justin Wade

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)

Justin Wade

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.