Help with complicated formula 🙋‍♂️

Mark Silvester

The spreadsheet in question is a shipping/inventory database:

In the screenshot above, the first rows represent the total amount of contracted devices. In this case, the client has ordered a total of 200 smart switches and 150 pairing rigs (remaining column = spare qty + total count).

The blue rows below the device count represent shipping phases (pictured above). I have a SUMIF formula in place in the "Remaining" column (pasted below) that calculates when a specific SKU is entered below in the shipping phase, then the "Remaining column" reflects what has been added or "shipped".

=([Total Count]@row - (SUMIF(SKU:SKU, SKU@row, [Total Count]:[Total Count]) - [Total Count]@row) + [Spare Qty]@row)

As you can see in the screenshot below, I've shipped 10 of each item and the remaining column adjusts accordingly:

This process works great. The problem I am having is in relation to Change Orders (CO). As the name suggests, a change order is when a client wants to Add or Remove an item. In the screenshot below, I've made use of the parent/child row feature to visualize a CO, deleted the "Remaining" formula from that row and have included change order (CO) columns:

In the example above, the client has decided to Add 50 smart switches. To calculate the quantity and "CO Type", I've included another SUMIFS formula in the "Remaining" column which is added to the original one pasted above. Please see below for the added piece of the formula and the complete final product:

SUMIFS([CO Qty]:[CO Qty], [CO Type]:[CO Type], "Add") - SUMIFS([CO Qty]:[CO Qty], [CO Type]:[CO Type], "Remove")

The complete formula:

=([Total Count]@row - (SUMIF(SKU:SKU, SKU@row, [Total Count]:[Total Count]) - [Total Count]@row) + [Spare Qty]@row) + SUMIFS([CO Qty]:[CO Qty], [CO Type]:[CO Type], "Add") - SUMIFS([CO Qty]:[CO Qty], [CO Type]:[CO Type], "Remove")

Alright, if you're still with me...

The problem I am having is that the Change Order (CO) should only be calculated in relation to that specific SKU. In the example above, when I added 50 extra smart switches, I just wanted the top smart switch line to calculate. I.e. The 240 in the "46562 Smart Switch" parent row "remaining" column is accurate, but it has added 50 to the entire remaining column. 🤦‍♂️

Is it possible for the CO to just calculate for the specific SKU? Maybe a VLOOKUP formula can be incorporated?

If anyone has any suggestions it would be incredibly appreciated! The company I work for is a startup and a support add-on is just not in the budget. This community has been an immense help.

Thank you!

Paul Newcome

You would need to include the SKU range/criteria set in your SUMIFS that calculate the Adds and Removes.

=([Total Count]@row - (SUMIF(SKU:SKU, SKU@row, [Total Count]:[Total Count]) - [Total Count]@row) + [Spare Qty]@row) + SUMIFS([CO Qty]:[CO Qty], [CO Type]:[CO Type], "Add", SKU:SKU, SKU@row) - SUMIFS([CO Qty]:[CO Qty], [CO Type]:[CO Type], "Remove", SKU:SKU, SKU@row)

Paul Newcome

You would need to include the SKU range/criteria set in your SUMIFS that calculate the Adds and Removes.

=([Total Count]@row - (SUMIF(SKU:SKU, SKU@row, [Total Count]:[Total Count]) - [Total Count]@row) + [Spare Qty]@row) + SUMIFS([CO Qty]:[CO Qty], [CO Type]:[CO Type], "Add", SKU:SKU, SKU@row) - SUMIFS([CO Qty]:[CO Qty], [CO Type]:[CO Type], "Remove", SKU:SKU, SKU@row)

Mark Silvester

Thank you so much, Paul. It such a great feeling seeing something work as intended!

Much appreciated as always.

Paul Newcome

Happy to help! 👍️

Please don't forget to flag the most appropriate response as "helpful". This will let others know that a solution has been found.