The formula should return 2 because only 2 containers are available. If, in
the example, this particular part is packed 350 to a container and I need
1000, and there were an unlimited number of containers available, the formula
should return 3. However, if there were only 2 containers avaialble, it
should return 2. Please note that this is only one of many items to be
shipped and the shipment (in every instance) is an aggregate of all the
items. I may have 2 containers of the first item, 5 of the second, 3 of the
third and so on. Although the number of containers for an individual item is
important, the total number of containers, for all items combined, cannot
exceed 24. If it does, then I have to 'cherry pick' and decide what to leave
off.

Although I don't believe this is critical, I should note that the materials
in question come off a production line and the final process results in each
container quantity being variable. Because of this, I should also note that
I use a second formula (in essence a duplicate of the first but instead of
counting containers it sums the quantity in the containers). So, going back
to the example of unlimited containers, the result shoud be 3 containers and
1,050 pcs.

My original formulas sufficed so long as no single item required more than 7
containers.

"loudfish" wrote:

On 29 May, 14:05, ronnomad wrote:
Your formula does work but only if the total quantity available to ship is
greater than the quantity needed. If, as in the example I gave, the quantity
available (700 pcs) is less than the quantity needed (1000 pcs), the formula
result is 0 (zero). In this instance the answer should be 2 because 2 is the
total number of containers available even though the quantity is less than
what is needed.

The only condition where the quantity available (700 pcs) is less than
the quantity needed (1000 pcs) should be once you have tried the last
container (the 24th one).

The second formula I gave should return 24 even if there are not
enough pieces in all 24 containers:

.... then change the last value to be summed from a conditional
IF(SUM(L37:AC37)K37*-1,24,999
to an absolute:
24

So let me restate back as a question. Why should your example above
return 2 instead of moving on to the 3rd container? (and the
4th, ..... until it either finds enough pieces, or reaches the 24th
container)

HTH

Andrew