Sum of data across multiple columns & rows based on Criteria

Copper Contributor

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.




3 Replies
best response confirmed by VI_Migration (Silver Contributor)




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

@Hans Vogelaar This worked perfectly! Thank you!

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