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
- Smartsheet Customer Resources
- 62.1K Get Help
- 352 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 135 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!