SOLVED
Home

Sumif with an AND requirement?

%3CLINGO-SUB%20id%3D%22lingo-sub-713224%22%20slang%3D%22en-US%22%3ESumif%20with%20an%20AND%20requirement%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-713224%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI'm%20trying%20to%20create%20a%20document%20whereby%20I%20can%20see%20which%20products%20I%20have%20sold%20to%20which%20customer.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20is%20a%20basic%20version%20-%20the%20Import%20tab%20I%20will%20paste%20in%20data%20exported%20from%20my%20MRP%20system%20so%20I%20need%20to%20keep%20the%20columns%20in%20this%20order%20-%20the%20only%20three%20columns%20I'm%20interested%20in%20are%20the%20ones%20I%20have%20part%20populated%20already.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20then%20want%20the%20%22All%20products%22%20tab%20to%20collate%20this%20for%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20managed%20to%20create%20a%20formula%20that%20added%20up%20the%20product%20quantity%2C%20but%20couldn't%20work%20out%20how%20to%20also%20have%20it%20customer%20specific.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20much%20appreciated%20-%20many%20thanks%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJamie.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-713224%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-713339%22%20slang%3D%22en-US%22%3ERe%3A%20Sumif%20with%20an%20AND%20requirement%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-713339%22%20slang%3D%22en-US%22%3EA%20pivot%20table%20would%20make%20this%20a%20simple%20task%2C%20no%20formulas%20required.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-713372%22%20slang%3D%22en-US%22%3ERe%3A%20Sumif%20with%20an%20AND%20requirement%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-713372%22%20slang%3D%22en-US%22%3EYou%20may%20use%20a%20simple%20SUMIFS%20in%20B5%2C%20copied%20down%20rows%20and%20across%20columns%2C%20like%20this%3A%3CBR%20%2F%3E%3DSUMIFS(Import!%24M%3A%24M%2C%3CBR%20%2F%3EImport!%24F%3A%24F%2C%24A5%2C%3CBR%20%2F%3EImport!%24H%3A%24H%2CB%243)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-713483%22%20slang%3D%22en-US%22%3ERe%3A%20Sumif%20with%20an%20AND%20requirement%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-713483%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3Bthat's%20perfect%20!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%2C%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJamie.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-713499%22%20slang%3D%22en-US%22%3ERe%3A%20Sumif%20with%20an%20AND%20requirement%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-713499%22%20slang%3D%22en-US%22%3EYou're%20very%20much%20welcome!%3C%2FLINGO-BODY%3E
Jamie Sproston
Occasional Contributor

Hi,

I'm trying to create a document whereby I can see which products I have sold to which customer.

 

Attached is a basic version - the Import tab I will paste in data exported from my MRP system so I need to keep the columns in this order - the only three columns I'm interested in are the ones I have part populated already.

 

I then want the "All products" tab to collate this for me.

 

I managed to create a formula that added up the product quantity, but couldn't work out how to also have it customer specific.

 

Any help much appreciated - many thanks in advance.

 

Jamie.

4 Replies
A pivot table would make this a simple task, no formulas required.
Solution
You may use a simple SUMIFS in B5, copied down rows and across columns, like this:
=SUMIFS(Import!$M:$M,
Import!$F:$F,$A5,
Import!$H:$H,B$3)

@Twifoo that's perfect !

 

Thank you so much, greatly appreciated.

 

Jamie.

You're very much welcome!
Related Conversations