How to use SUMIFS with dynamic criteria?


New Member
May 24, 2019
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.


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

Forum statistics

Latest member
Joaquin Phoenix

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...