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
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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!