Help w/SUMPRODUCT with Three Arrays/Conditions/AND

[GreganDunn]

I'm pretty sure that SUMPRODUCT is the solution to my problem here. I'm new to the SUMPRODUCT formula and fully understand it's basics and straight forward usage, however my SUMPRODUCT usage/requirement is a little more complex are I'm struggling with finding the right syntax to make things work for me.

I'm attempting to create a new data set that lists the FRAGMENTS (or components) needed for a given UPGRADE based on the ITEM (items are composed of fragments) requirements for an upgrade and the RECIPE of the FRAGMENTS to create an ITEM.

I have an insane spreadsheet to plug this into, but I've narrowed it down and simplified it to the attached spreadsheet. The whole vertical/horizontal array thing is screwing me up in my specific example.

Thanks for taking the time to have a peak at my challenge, I really appreciate it!


Example File: https://www.dropbox.com/s/rqwq372hs2ome35/Upgrade Item to Fragments.xlsx?dl=0

Screenshot:

 

[GreganDunn]

This wall of numbers (and this goes on for several hundred cells in each direction!) made me very happy. Thank you for teaching me something today sheetspread, I really appreciate it.

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules. Be sure to follow & read the link at the end of the rule too!

Cross posted at: https://www.excelforum.com/excel-formulas-and-functions/1238694-help-w-sumproduct-with-three-arrays-conditions-and.html

If you do cross-post in the future and also provide a link, then there shouldn’t be a problem.