SOLVED

Sum of data across multiple columns & rows based on Criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-3335713%22%20slang%3D%22en-US%22%3ESum%20of%20data%20across%20multiple%20columns%20%26amp%3B%20rows%20based%20on%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3335713%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20data%20set%20that%20ranges%20across%20multiple%20rows%20and%20columns.%26nbsp%3B%20I%20am%20building%20a%20nested%20formula%20and%20looking%20to%20sum%20QTY%20based%20on%20multiple%20criteria%20in%20one%20part%20of%20it.%26nbsp%3B%20I%20tried%20sumifs%2C%20however%2C%20that%20does%20not%20work.%26nbsp%3B%20Below%20is%20my%20example%20using%20a%20hypothetical%20sumifs%20(keeping%20in%20mind%2C%20this%20does%20not%20work).%26nbsp%3B%20Any%20suggestions%20on%20how%20to%20return%20my%20expected%20result%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22BFellner82_0-1651872623743.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F369912iF69027126EB9C593%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22BFellner82_0-1651872623743.png%22%20alt%3D%22BFellner82_0-1651872623743.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3335713%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3335788%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20of%20data%20across%20multiple%20columns%20%26amp%3B%20rows%20based%20on%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3335788%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1383365%22%20target%3D%22_blank%22%3E%40BFellner82%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT('PROD%26amp%3BDMD'!%24G%244%3A%24AF%242099%2C%20('PROD%26amp%3BDMD'!%24A%244%3A%24A%242099%3D%24A4)*('PROD%26amp%3BDMD'!%24G%242%3A%24AF%242%3D%24M4)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3343708%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20of%20data%20across%20multiple%20columns%20%26amp%3B%20rows%20based%20on%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3343708%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BThis%20worked%20perfectly!%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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.

BFellner82_0-1651872623743.png

 

 

2 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

@BFellner82 

Try

 

=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!