CODEWITHSUNDEEP
excelvlookupcountifsumifsvba
user4223089
user4223089

Reputation: 19

Use SUMIF and VLOOKUP functions together

This is my problem:

Sheet 1 - Column A has "Item #" 
Sheet 2 - Column C has "Item #" 
Sheet 1 - Column E will have the formula 
Sheet 2 - Column G has "Item Qty" 
Sheet 2 - Column B has "Size"

What I want is to return sum of 'Item Qty' of a certain 'Item #' based on 'Size'.

If Sheet1(A2) has Item # 123 I would like it to find all 123 Item #s in Sheet2 and return the sum of all those numbers ONLY IF the "Size" column equals my criteria, let's say "Medium".

Upvotes: 0

Views: 6116

Answers (1)

pnuts
pnuts

Reputation: 59475

Please try: 

=SUMIFS(Sheet2!G:G,Sheet2!C:C,Sheet1!A2,Sheet2!B:B,"Medium")

Above would be more flexible as: 

=SUMIFS(Sheet2!$G:$G,Sheet2!$C:$C,Sheet1!$A2,Sheet2!$B:$B,Sheet1!E$1)

where E1 contains "Medium". Then it could be copied across (say under F1 etc containing "Large" ...) to add up quantities for Large ... sizes of the item in A2 (and copied down for a different item # in A3 of Sheet1).

Upvotes: 2

Related Questions