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

First add the below 2 columns:
 Row ID. System auto number
 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)
Answers

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, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
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:
 Row ID. System auto number
 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
Categories
Check out the Formula Handbook template!