VLOOKUP
 

I am running a vlookup for part #'s at my company. The table that I have may
sometimes contain the same part numbers multiple times. I want my vlookup to
look at all of the same part number and give me a total of the column that
I'm looking in. For example

PART # QUANTITY
15300001 100
15906231 52
15308526 78
15300001 69

Now when I do a lookup on a separate worksheet underneath the part number
15300001 I want my quantity to show 169.

Is this possible?...Please help.

Thanks in advance.

Travis Dahlman

you have to sum the data first to use VLOOKUP
OR try using the SUMIF function.
it will let you do what you want. (*** There's several ...IF functions,
countif, sumif, etc..)



"Dahlman" wrote:

I am running a vlookup for part #'s at my company. The table that I have may
sometimes contain the same part numbers multiple times. I want my vlookup to
look at all of the same part number and give me a total of the column that
I'm looking in. For example

PART # QUANTITY
15300001 100
15906231 52
15308526 78
15300001 69

Now when I do a lookup on a separate worksheet underneath the part number
15300001 I want my quantity to show 169.

Is this possible?...Please help.

Thanks in advance.

Travis Dahlman

You may want to consider using a pivot table to sum up the totals.

http://www.cpearson.com/excel/pivots.htm

"Dahlman" wrote:

I am running a vlookup for part #'s at my company. The table that I have may
sometimes contain the same part numbers multiple times. I want my vlookup to
look at all of the same part number and give me a total of the column that
I'm looking in. For example

PART # QUANTITY
15300001 100
15906231 52
15308526 78
15300001 69

Now when I do a lookup on a separate worksheet underneath the part number
15300001 I want my quantity to show 169.

Is this possible?...Please help.

Thanks in advance.

Travis Dahlman

Currently my formula is as follows: =VLOOKUP(B5,RELEASE3!B4:G1242,6,0)
"RELEASE" is the name of the worksheet that my datarange is in. Can you
explain how i would use a sumif statement with this.....or if it's totally
different let me know too. I don't quite understand how to incorporate that
in with my VLOOKUP. I've never had to do that before. Thanks again

Travis Dahlman

"Dahlman" wrote:

I am running a vlookup for part #'s at my company. The table that I have may
sometimes contain the same part numbers multiple times. I want my vlookup to
look at all of the same part number and give me a total of the column that
I'm looking in. For example

PART # QUANTITY
15300001 100
15906231 52
15308526 78
15300001 69

Now when I do a lookup on a separate worksheet underneath the part number
15300001 I want my quantity to show 169.

Is this possible?...Please help.

Thanks in advance.

Travis Dahlman

you use the sumIF instead of the vlookup..
it's syntax is : Sumif(range,criteria,sumrange)
so I THINK it would be like this

= sumif(RELEASE3!$B$4:$G:$1242,B5,6)

range is the range of cells to analyze, ASSUMING the first column is the one
you are going to compare to criteria.


"Dahlman" wrote:

Currently my formula is as follows: =VLOOKUP(B5,RELEASE3!B4:G1242,6,0)
"RELEASE" is the name of the worksheet that my datarange is in. Can you
explain how i would use a sumif statement with this.....or if it's totally
different let me know too. I don't quite understand how to incorporate that
in with my VLOOKUP. I've never had to do that before. Thanks again

Travis Dahlman

"Dahlman" wrote:

I am running a vlookup for part #'s at my company. The table that I have may
sometimes contain the same part numbers multiple times. I want my vlookup to
look at all of the same part number and give me a total of the column that
I'm looking in. For example

PART # QUANTITY
15300001 100
15906231 52
15308526 78
15300001 69

Now when I do a lookup on a separate worksheet underneath the part number
15300001 I want my quantity to show 169.

Is this possible?...Please help.

Thanks in advance.

Travis Dahlman

It tells me that this is an incorrect formula. If you look at it again do you
any mistakes you may have made in typing it? I thought that maybe it shoudl
be
=Sumif(RELEASE3!$B$4:$G$1242,B5,6) but that still didn't work. I"m going to
continue my research on how to format a sumif formula but if you can think of
anything then please let me know. Thanks

Travis dahlman


"TomHinkle" wrote:

you use the sumIF instead of the vlookup..
it's syntax is : Sumif(range,criteria,sumrange)
so I THINK it would be like this

= sumif(RELEASE3!$B$4:$G:$1242,B5,6)

range is the range of cells to analyze, ASSUMING the first column is the one
you are going to compare to criteria.


"Dahlman" wrote:

Currently my formula is as follows: =VLOOKUP(B5,RELEASE3!B4:G1242,6,0)
"RELEASE" is the name of the worksheet that my datarange is in. Can you
explain how i would use a sumif statement with this.....or if it's totally
different let me know too. I don't quite understand how to incorporate that
in with my VLOOKUP. I've never had to do that before. Thanks again

Travis Dahlman

"Dahlman" wrote:

I am running a vlookup for part #'s at my company. The table that I have may
sometimes contain the same part numbers multiple times. I want my vlookup to
look at all of the same part number and give me a total of the column that
I'm looking in. For example

PART # QUANTITY
15300001 100
15906231 52
15308526 78
15300001 69

Now when I do a lookup on a separate worksheet underneath the part number
15300001 I want my quantity to show 169.

Is this possible?...Please help.

Thanks in advance.

Travis Dahlman

Hi,
Try
=SUMIF(RELEASE3!Part#range,Part#,RELEASE3!Quantity range)
where Part#range and Quantityrange are the column-ranges for Part# and
Quantity , and Part# is cell-address for the part you want to find the total
quantity for. The formmula goes into the cell that would show the answer.
When i tried using the example data you have given, the answer came up
correctly as 169.
Regards,
B.R.Ramachandran

"Dahlman" wrote:

It tells me that this is an incorrect formula. If you look at it again do you
any mistakes you may have made in typing it? I thought that maybe it shoudl
be
=Sumif(RELEASE3!$B$4:$G$1242,B5,6) but that still didn't work. I"m going to
continue my research on how to format a sumif formula but if you can think of
anything then please let me know. Thanks

Travis dahlman


"TomHinkle" wrote:

you use the sumIF instead of the vlookup..
it's syntax is : Sumif(range,criteria,sumrange)
so I THINK it would be like this

= sumif(RELEASE3!$B$4:$G:$1242,B5,6)

range is the range of cells to analyze, ASSUMING the first column is the one
you are going to compare to criteria.


"Dahlman" wrote:

Currently my formula is as follows: =VLOOKUP(B5,RELEASE3!B4:G1242,6,0)
"RELEASE" is the name of the worksheet that my datarange is in. Can you
explain how i would use a sumif statement with this.....or if it's totally
different let me know too. I don't quite understand how to incorporate that
in with my VLOOKUP. I've never had to do that before. Thanks again

Travis Dahlman

"Dahlman" wrote:

I am running a vlookup for part #'s at my company. The table that I have may
sometimes contain the same part numbers multiple times. I want my vlookup to
look at all of the same part number and give me a total of the column that
I'm looking in. For example

PART # QUANTITY
15300001 100
15906231 52
15308526 78
15300001 69

Now when I do a lookup on a separate worksheet underneath the part number
15300001 I want my quantity to show 169.

Is this possible?...Please help.

Thanks in advance.

Travis Dahlman

Thank you all very much. Everyone helped a lot. I appreciate it.

Travis Dahlman