SumIF until current row in a range


Hi all,

I have a sheet keeping invoices, and I would like to see the delta vs the budget.

My Current structure in the invoice smartsheet:

Name : getting information from the budget file

Invoice Amount: entered

Budget Remaining: formula =VLOOKUP(Name@row; {Budget_22}; 8; false) - SUMIF((Name:Name); Name@row; ([Invoice Amount]:[Invoice Amount]))

The formula is correct and results in the following outcome

However I would need to have this result

But than I can not take the entire Name range, and should stop the range at the current row?

Any suggestions

Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

    @Wim Nagels

    First add the below 2 columns:

    1. Row ID. System auto number
    2. ROW#. Column Formula: =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)

    Then your 'Budget Remaining' formula would be:

    =VLOOKUP(Name@row; {Budget_22}; 8; false) - SUMIFS([Invoice Amount]:[Invoice Amount];Name:Name; Name@row;[ROW#]:[ROW#]; @cell<=[ROW#]@row)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!