# 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

• ✭✭✭✭✭✭

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)

• ✭✭✭✭✭✭
edited 03/10/22

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!

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭

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)

• ✭✭✭

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!