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

Find more posts tagged with

Accepted answers

Leibel S

@Wim Nagels

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)

All comments

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:

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)

Wim Nagels

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.