Sum of data across multiple columns & rows based on Criteria

[BFellner82]

I have a data set that ranges across multiple rows and columns.  I am building a nested formula and looking to sum QTY based on multiple criteria in one part of it.  I tried sumifs, however, that does not work.  Below is my example using a hypothetical sumifs (keeping in mind, this does not work).  Any suggestions on how to return my expected result would be greatly appreciated.

[#2 · Accepted solution]

@BFellner82 

Try

=SUMPRODUCT('PROD&DMD'!$G$4:$AF$2099, ('PROD&DMD'!$A$4:$A$2099=$A4)*('PROD&DMD'!$G$2:$AF$2=$M4)

[#3]

@Hans Vogelaar This worked perfectly! Thank you!

[#4]

OMG I didn't think this was possible! you have saved me hours of changing my data structure - THANK YOU!