# How to use SUMIFS with dynamic criteria?

#### juniorkrebs

##### New Member
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.

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### yky

##### Well-known Member
Instead of a formula solution, maybe you should consider a VBA solution.

Replies
1
Views
438
Replies
2
Views
101
Replies
1
Views
77
Replies
3
Views
503
Replies
2
Views
2K

1,127,098
Messages
5,622,683
Members
415,920
Latest member
ExcelNoob28

### 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.

### Which adblocker are you using?

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

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