SUMIFS + LOOKUP? Inventory management combined with production schedule

Greetings,

I'm trying to create an extensive book that will handle many tasks and calculations for my job. I'm a bit of an Excel noob unfortunately, and am having trouble getting figuring out what functions to use for one of the first core tasks I'd like it to perform; inventory management.

What I'd like (specific to this) is to have one sheet for my 'task' schedule, one for receiving ingredients, one with my rolling inventory of needed ingredients, and one with the 'recipes' for these tasks. In each of these I have tables, with named ranges in these tables for things such as ingredients, dates, brands, etc. I'd like it such that by creating a schedule and tracking receiving, the rolling inventory will account for both received ingredients and scheduled output automatically. Summing the in and out is simple, as is the "subtotal" receiving/in, but I'm stuck on the "subtotal" out/scheduled production.

I'm looking for something to the effect of:
=SUMIFS((SUM(Ingredients1amount:Ingredients5amount...)*(COUNTIF(Task_Dates, Task_Dates<=TODAY()), (HLOOKUP(Tasks, Recipes, Recipe List, FALSE))), (Ingredients1:Ingredients5...), "name of ingredient for this row=(Indredients1:Ingredients5), Task, Task=Recipe))

.. where for that ingredients' "out subtotal", it will look at the different scheduled tasks before and up to today, and look through those scheduled tasks' recipes and if that ingredient is a component, sum that as many times as that task appears in the schedule, and do that for all tasks that may or may not contain this ingredient. Evidently, the syntax and/or functions above are not working, perhaps because I've made them too circular.

Am I on the right track? Is their a more straightforward way to go about this, if a I take a step back and reorganize my data (Pivot Tables)? Is their a wonderful function I'm missing out on? I'll need to be doing something similar to this throughout the book if it's to function as I'd like it too, i.e. being able to lookup and manipulate data in one table by matching it's header/category reference to input in another cell, nestled such that I can perform the needed manipulation and calculation.

Attached is the sad work in progress.

Thanks for your time, and any help is much appreciated.
Cheers