What is the formula for a cell using a few inputs based on a Part #

Please see below. What I'm trying to do is write a formula for the Remaining column to calculate Footage minus the absolute value of Starting # minus Ending # based on the Part #.

For example, Part123 has the following:

Footage = 10000, Starting # = 5000, Ending # = 3000

Remaining should be 8000. Then the next line for Part123 should be based on the previous Remaining value for that Part # which is 8000 minus absolute value of 3000 minus 2000 equals 7000.

As you can see, Part123 doesn't show up again until a few lines after. This is when I need it to calculate based off the previous Remaining value for that Part # which is 7000 minus absolute value of 2000 minus 1000 equals 6000.

How would I accomplish this?

Thanks in advance!

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

I would insert an auto-number column with no special formatting first. Then you would use something along the lines of...

=SUMIFS(Footage:Footage, [Part #]:[Part #], @cell = [Part #]@row, [Auto-Number]:[Auto-Number], @cell <= [Auto-Number]@row) - (SUMIFS([Starting #]:[Starting #], [Part #]:[Part #], @cell = [Part #]@row, [Auto-Number]:[Auto-Number], @cell <= [Auto-Number]@row) - SUMIFS([Ending #]:[Ending #], [Part #]:[Part #], @cell = [Part #]@row, [Auto-Number]:[Auto-Number], @cell <= [Auto-Number]@row))

All comments

Paul Newcome

I would insert an auto-number column with no special formatting first. Then you would use something along the lines of...

Kris M

@Paul Newcome Thanks!!! This worked, except I need to add the Absolute Value of Starting # - Ending # because the Ending # could be greater than the Starting #, but I need the output to be a positive value. Where would I insert this ABS function in the formula?

Paul Newcome

Try this:

=SUMIFS(Footage:Footage, [Part #]:[Part #], @cell = [Part #]@row, [Auto-Number]:[Auto-Number], @cell <= [Auto-Number]@row) - ABS(SUMIFS([Starting #]:[Starting #], [Part #]:[Part #], @cell = [Part #]@row, [Auto-Number]:[Auto-Number], @cell <= [Auto-Number]@row) - SUMIFS([Ending #]:[Ending #], [Part #]:[Part #], @cell = [Part #]@row, [Auto-Number]:[Auto-Number], @cell <= [Auto-Number]@row))

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.