Using VLOOKUP to find multiple occurrences and find the Total quantity

I would like to find Total quantity for the products, which shares same stock number in a table. I like to use VLOOKUP if it is possible. Does anyone know how to do? Do I have to use another function for this?

Originally Posted by susanpa

I would like to find Total quantity for the products, which shares same stock number in a table. I like to use VLOOKUP if it is possible. Does anyone know how to do? Do I have to use another function for this?

You probably want to use the SUMPRODUCT function.

For example, if your Stock Number is in Column A and the Quantity is in column B, you could do something like this:

If the product stock number is "X100":
=SUMPRODUCT(--($A$2:$A$50="X100"),$B$2:$B$50)

If the product stock number is in cell E1:
=SUMPRODUCT(--($A$2:$A$50=E1),$b$2:$B$50)

There are some tutorials on how to use SUMPRODUCT floating around.

One link I have is http://www.xldynamic.com/source/xld.SUMPRODUCT.html. Someone else might have another link for you if you need it, or you can just do a search.

HTH,
Scott

Thank you.

Susan

Originally Posted by susanpa

Thank you.

Susan

I'm sorry, I'm so used to using SUMPRODUCT, I forgot about the other function you can use, namely SUMIF.

For the above, you could go:
=SUMIF($A$2:$A$50,"X100",$B$2:$B$50)
or
=SUMIF($A$2:$A$50,E1,$B$2:$B$50)

SUMPRODUCT is more versatile, but SUMIF does exactly what you're asking.

Scott

SUMPRODUCT worked very well for me. Thanks Again.