Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
Thank you all very much. Everyone helped a lot. I appreciate it.
Travis Dahlman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
Array Function with VLOOKUP | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
VLOOKUP not working | Excel Worksheet Functions |