How to sum cell values until it reaches a value then count the number of rows in a dynamic table

[sciphinupe]

Hello Everyone,

I am in a dilemma I hope some wise person can help solve. I have a dynamic table consisting of 150+ rows with 3 main categories I'm looking to extract information. I've tried to use all forms of nesting SUMIFS & COUNTIFS and arrays but I can't figure it out and haven't seen any video form Mr. Excel on this topic. I'm hoping someone can share a formula or information that will:

1. Sum the number of orders exclusively for each part but only UP TO THE AMOUNT OF STOCK ON HAND

2. Count the number of rows it took for the number of orders to be as close as or equal to the stock on hand

For example, in the chart below there are 13 rows of orders/customers for 2 separate part numbers. Part A has 15 orders however there are only 2 in stock, and part B has 470 orders for however we only have 39 in stock.

I would like a formula I can use (for all the part number in my table) to be able to look at the 6 rows of part A and add cells in increments until the order number doesn't surpass stock on hand (SOH) number; 2. Also, to do the same thing for the 7 rows for part B.

[table="width: 500, class: grid, align: left"]
[tr]
[td]Customers
[/td]
[td]PN[/td]
[td]Orders[/td]
[td]Stock on hand[/td]
[/tr]
[tr]
[td]1
[/td]
[td]Part A[/td]
[td]1[/td]
[td]2[/td]
[/tr]
[tr]
[td]2[/td]
[td]Part A[/td]
[td]3[/td]
[td]2[/td]
[/tr]
[tr]
[td]3[/td]
[td]Part A[/td]
[td]5[/td]
[td]2[/td]
[/tr]
[tr]
[td]4[/td]
[td]Part A[/td]
[td]2[/td]
[td]2[/td]
[/tr]
[tr]
[td]5[/td]
[td]Part A[/td]
[td]2[/td]
[td]2[/td]
[/tr]
[tr]
[td]6[/td]
[td]Part A[/td]
[td]2[/td]
[td]2[/td]
[/tr]
[tr]
[td]7[/td]
[td]Part B[/td]
[td]100[/td]
[td]39[/td]
[/tr]
[tr]
[td]8[/td]
[td]Part B[/td]
[td]100[/td]
[td]39[/td]
[/tr]
[tr]
[td]9[/td]
[td]Part B[/td]
[td]38[/td]
[td]39[/td]
[/tr]
[tr]
[td]10[/td]
[td]Part B[/td]
[td]100[/td]
[td]39[/td]
[/tr]
[tr]
[td]11[/td]
[td]Part B[/td]
[td]100[/td]
[td]39[/td]
[/tr]
[tr]
[td]12[/td]
[td]Part B[/td]
[td]1[/td]
[td]39[/td]
[/tr]
[tr]
[td]13[/td]
[td]Part B[/td]
[td]31[/td]
[td]39[/td]
[/tr]
[/table]



When calculating correctly I would like the formula to show for Part A one customer's order can be filled (because there is 2 SOH) but for Part B two customer's orders can be filled (customer 12 has 1 order & customer 13 has 31 orders; therefore, these two smaller orders within Part B's SOH should be satisfied first before that of customer 9's order).

I understand it's a lot to digest so hopefully someone will have an idea how I can tackle this issue. In summary, I need to understand how many customers' order lines have or have not been satisfied given the amount of available stock for each particular part. None the videos I watch for information I could find so far addresses how to count the number of remaining rows of a particular part after excluding rows that has met a criteria.

Thanks all so much!!!

Greg

 

[sciphinupe]

Afternoon Peter,

Yes, so far it seems as if your modification works. Thanks again! I will continue to "test-drive" the formula to validate it can solve all circumstances.

Warmest regards and Happy Holidays,

Greg

 

[Peter_SSs]

Afternoon Peter,

Yes, so far it seems as if your modification works. Thanks again! I will continue to "test-drive" the formula to validate it can solve all circumstances.

OK, thanks for letting us know.

Warmest regards and Happy Holidays,

Greg

Thank you. Same to you.