How to use SUMIFS with dynamic criteria?

juniorkrebs

New Member
Joined
May 24, 2019
Messages
1
I don't even know how I'll explain this for you, here's the situation:
Everyday at work we receive a "recipe" with the amount of ingredients we'll need to use that day to manufacture our products (we use the FEFO system to pick the ingredients). Today there's a person who compares the quantity needed in the recipe with our warehouse inventory, than he fills a worksheets with every ingredient and their corresponding batch numbers (i.e. we're going to need 100kg of calcium carbonate, he writes "calcium carbonate - 100 kg - 4 bags - 01234/01235/01236" (these numbers are the batch numbers from the calcium carbonate). This worksheet is used as a reference to other employees who are responsible to find and pick these batches in the inventory to make the weighting processes quicker and easier. Also, there's two kind of ingredients: those that are in use and those that are new, I already made a VBA script to classify them and put each kind in a different worksheet.

Ok, history told, now let's go to the real problem:

You've noticed that the batch numbers are a manual input, right? I want to change it and make it automatic, so I use VLOOKUP to find the batch that is in use and the next one (the new) if the batch in use doesn't have the amount needed. The problem is, how can I find the next batches if the first new one doesn't have the quantity I need? I can't use INDEX+MATCH because sometimes we don't have that ingredient anymore and then the formula would return me the batch from another ingredient.

Suppose I have to use 100 kg of calcium carbonate again, the batch that is in use has 27 kg, in order to complete the 100 kg I'll have to look at my inventory and pick the next based on FEFO, so, I'll need 73 kg from the "new" ingredients, the problem starts when the next new batches are less than 73 kg, because I'd have to list more than one batch number.

I thought that I could solve it with some kind of SUMIFS, but I couldn't figure out how to implement it.

My rule would have to be something like this: if (inventory - amount of the recipe)>0 than it would list the batch in use, otherwise it would have to list every new batch used in the sum (sometimes it could be just one, sometimes it could be four, maybe ten) until the sum result becomes a positive number.

So, how do I do it? It looks impossible to me.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,833
Office Version
  1. 2010
Platform
  1. Windows
Instead of a formula solution, maybe you should consider a VBA solution.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,326
Messages
5,624,019
Members
416,006
Latest member
PCaffrey

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