Subtracting Column until it reach 0 then move on

Devas

New Member
Joined
Aug 16, 2013
Messages
7
I have a report that I want to show how many sales items can be filled with what we have in stock, but there will be multiple items in one column. For example:

Product A = 5 in stock
Product B = 15 in stock
Product C = 11 in stock

ABCDE
1ProductOrderBack OrderIn StockQty
2A20A5
3A30B15
4A22C11
5B50
6B20
7B30
8B40
9B54
10C40
11C30
12C20
13C31

<tbody>
</tbody>

The formula I used for C2 is MAX(SUM($B$2:B2)-$E$2,0) down to C4 MAX(SUM($B$2:B4)-$E$2,0). The thing I want to know is if there is a way to make the SUM(RANGE) part start from the first column that a new product starts, so that for column C5 (start of product B) it would be MAX(SUM($B$5:B5)-$E$3,0) down to =MAX(SUM($B$5:B9)-$E$3,0).

Can anyone help?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Hi and welcome to Mr Excel Forum

Maybe...

C2
=MAX(0,SUMIF($A$2:A2,A2,$B$2:B2)-INDEX($E$2:$E$4,MATCH(A2,$D$2:$D$4,0)))
copy down

M.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top