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?
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,567
Messages
5,596,908
Members
414,110
Latest member
docops

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
Top