Formula to referance availible remaining stock based off used stock

Caleb W

I am trying to build a sheet to track a number a different brace types. How would I build the formula to take the total owned braces by type, subtract total used by type across several rows.

This would be the place holder for reference of type and total available owned.

This would be the entry as braces are assigned out to a project.

So I would want the Quantity column to minus from the Quantity Owned column by type leaving just what are not assigned out.

Carson Penticuff

I notice there is a Date Return column. Is it possible there will be a date in this column for some rows, indicating that the braces have been returned?

Caleb W

@Carson Penticuff yes that is the plan. Once the return date is set then it would be entered into that column. Then once that date is reached, the days on site would stop counting and the braces would then revert back into the available braces.

Carson Penticuff

Here is what I envision, please correct me if I misunderstand.

=IF(AND([Brace]@row <> "", [Quantity Owned]@row <> ""), [Quantity Owned]@row - SUMIFS({Quantity}, {Brace Type}, [Brace]@row, {Date Return}, ""))

I have included only the relevant columns in the sample sheets I show above. This would require that you create the following cross-sheet references in the first sheet, pointing at columns in the second sheet. {Quantity} - {Brace Type} - {Date Return}

Caleb W

Can this be all in one sheet?

Carson Penticuff

To be clear, do you mean you would like ALL of the information in one sheet? The two screenshots I posted are representative of the two that you posted. My second sheet just doesn't contain all of the extra columns, Cost, Days on Site, etc. If you DO want everything on one sheet, that can also be done:

=IF(AND([Brace]@row <> "", [Quantity Owned]@row <> ""), [Quantity Owned]@row - SUMIFS([Quantity]:[Quantity], [Brace Type]:[Brace Type], [Brace]@row, [Date Return]:[Date Return], ""))

Once again, unnecessary columns are omitted.

Caleb W

Thank you @Carson Penticuff, that worked out perfect!!

One last questions for you. I am using =NETDAYS([Date Out]@row, TODAY()) to track days onsite since "date out". How can I close out and stop counting days once the date in "date return" column is reached?

Carson Penticuff

This will work the same as your current formula if [Date Return] is blank. If [Date Return] is populated, it will give you the number of days between [Date Out] and [Date Return].

=IF(ISDATE([Date Out]@row), IF(ISDATE([Date Return]@row), NETDAYS([Date Out]@row, [Date Return]@row), NETDAYS([Date Out]@row, TODAY())), "")

Caleb W

Thank you for the quick response. That is a big help.