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.

Answers

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

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

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    @Justin Wade

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

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


  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

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