SUMIF, VLOOKUP and SUMPRODUCT formula

Question

So I have this following spreadsheet.

Shipping WS

Col B is part number, Col C Where to ship. Col J is how many full skid and Col L how many more boxes to be added to full skip. There is a second sheet with the parts details like qty/box, box/skid, skid weight, etc...

Because everyday the is different, the number of lines in the first sheet will be different.

What I would like is a single cell at the top of the sheet that will give me total boxes going to each location that we ship to. So I want the sum of boxes for every lines that have NEWB as it destination as well as SANF. So each line calculation would be Value in K (number of full skid) * VLOOKUP of the part to get the box per skid (box per skid) + Value in L (number of loose boxes not making a full skid).

Is there a function that will do all of that for NEWB in one cell and SANF on another? The user enters the line as he goes so they are not all in groups. I might get 3 line of SANF then 2 of NEWB, then a couple of SANF and so forth.

Thanks,

Denis

Answer 1

I figured a way to do what I needed this weekend.

=SUMPRODUCT(K7:K52,IF(D7:D52="NEWB",1,0)*VLOOKUP(B7:B52,'Parts Data'!C3:H39,4,FALSE))

Basically I do a multiplication on every row no matter what with the sumproduct command. Number of skid (Col K) times the result of the IF statement. The IF statement will return the product of 1 times the vlookup value if the right location in Col D is what my formula is calculated for (NEWB) so basically the value of the vlookup is multiplied but number of skid for the sum. If the location is "SANF" then the product of the if command will be 0 times the value of vlookup which will give me 0, then time the number of skids, which will be 0 used for the sum.

I will copy and paste the same formula into a new cell and change "NEWB" to "SANF" in the formula for the calculation for the second location.

Hope this will help someone later.

Denis