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
<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?
Product A = 5 in stock
Product B = 15 in stock
Product C = 11 in stock
| A | B | C | D | E | |
| 1 | Product | Order | Back Order | In Stock | Qty |
| 2 | A | 2 | 0 | A | 5 |
| 3 | A | 3 | 0 | B | 15 |
| 4 | A | 2 | 2 | C | 11 |
| 5 | B | 5 | 0 | ||
| 6 | B | 2 | 0 | ||
| 7 | B | 3 | 0 | ||
| 8 | B | 4 | 0 | ||
| 9 | B | 5 | 4 | ||
| 10 | C | 4 | 0 | ||
| 11 | C | 3 | 0 | ||
| 12 | C | 2 | 0 | ||
| 13 | C | 3 | 1 |
<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?
