SumIF until current row in a range

Wim Nagels

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

Leibel S

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

Jeff Reisman

@Wim Nagels

This is not easy to do... in fact, once I tested what I came up with and posted here, I realized it wouldn't work.... so i erased it. Sorry!

Leibel S

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

Wim Nagels

Works perfect! Big thanks. this really made the day easier :-)