# Finding Cumulative Inventory Availability | Running Balance Total

0

Greetings TeachExcel.com & Forum Members:

I am trying to find the available balance of inventory for multiple and sometimes duplicate products down a column.

COLUMN A: Order #

COLUMN B: Product

COLUMN C: Order Quantity

COLUMN D: Available Inventory

COLUMN E: (Helper column) Balance of C & D

COLUMN F: Is where I am trying to show the quantity of products available minus the quantity ordered for that product, as I go down the row, some products are duplicated, and need to reflect the previous remaining balance of that product and subtract it for the next use case of that same product down the column.

EXAMPLE: is the first product in cell B2 is CALCZO. This product is replicated on the last order: cell B62, CALCZO.

SO: Cell F2 should equal 652, Cell F62 should equal 302 | or the balance of the last order in E2, minus the next ordered quantity on C62 of 350.

1

Please try this formula in F2.

``=\$D2-SUMIF(\$B\$2:\$B2,\$B2,\$C\$2:\$C2)``

Observe that the criteria and sum ranges expand as the formula is copied down to always start in row 2 and end in the row of the formula. Therefore otherwise qualifying entries below the formula row are not included in the total drawn.

### Discussion

ahhh you beat me! AND your solution is considerably more elegant than mine was going to be haha :P.  Simple and elegent is always the best way!
don (rep: 1745) Jun 19, '19 at 10:27 pm
Thank you Don. This praise definitely made my morning, coming from you :P
BTW you seem to have missed the off-topic comment I made below one of my answers a while ago. I pointed out that pasting VBA code into the edit pane inserts blank rows which disappear upon uploading if code tags were used but not if they were omitted. There should be an easy cure for this. I guess it's how VBA's CRs are interpreted. Please take a look at it.
Variatus (rep: 3533) Jun 19, '19 at 10:39 pm
This saved the day for my team! Thank you guys so much. I was sniffing around and very close, but your solution is perfect, and elegantly explained.

Thank You Variatus + Don :)
jonsully22 (rep: 2) Jun 20, '19 at 1:23 pm