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!
Best Answer

I would insert an autonumber column with no special formatting first. Then you would use something along the lines of...
=SUMIFS(Footage:Footage, [Part #]:[Part #], @cell = [Part #]@row, [AutoNumber]:[AutoNumber], @cell <= [AutoNumber]@row)  (SUMIFS([Starting #]:[Starting #], [Part #]:[Part #], @cell = [Part #]@row, [AutoNumber]:[AutoNumber], @cell <= [AutoNumber]@row)  SUMIFS([Ending #]:[Ending #], [Part #]:[Part #], @cell = [Part #]@row, [AutoNumber]:[AutoNumber], @cell <= [AutoNumber]@row))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Answers

I would insert an autonumber column with no special formatting first. Then you would use something along the lines of...
=SUMIFS(Footage:Footage, [Part #]:[Part #], @cell = [Part #]@row, [AutoNumber]:[AutoNumber], @cell <= [AutoNumber]@row)  (SUMIFS([Starting #]:[Starting #], [Part #]:[Part #], @cell = [Part #]@row, [AutoNumber]:[AutoNumber], @cell <= [AutoNumber]@row)  SUMIFS([Ending #]:[Ending #], [Part #]:[Part #], @cell = [Part #]@row, [AutoNumber]:[AutoNumber], @cell <= [AutoNumber]@row))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

@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?

Try this:
=SUMIFS(Footage:Footage, [Part #]:[Part #], @cell = [Part #]@row, [AutoNumber]:[AutoNumber], @cell <= [AutoNumber]@row)  ABS(SUMIFS([Starting #]:[Starting #], [Part #]:[Part #], @cell = [Part #]@row, [AutoNumber]:[AutoNumber], @cell <= [AutoNumber]@row)  SUMIFS([Ending #]:[Ending #], [Part #]:[Part #], @cell = [Part #]@row, [AutoNumber]:[AutoNumber], @cell <= [AutoNumber]@row))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!