VLOOKUP #45

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
0
Dahlman (27)
6/2/2005 6:43:10 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
748 Views

Similar Articles

[PageSpeed] 37

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
0
TomHinkle (87)
6/2/2005 6:46:01 PM
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
0
BarbR (262)
6/2/2005 6:47:25 PM
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
0
Dahlman (27)
6/2/2005 7:18:05 PM
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
0
TomHinkle (87)
6/2/2005 7:52:01 PM
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
0
Dahlman (27)
6/2/2005 8:27:34 PM
Hi,
Try
=SUMIF(RELEASE3!Part#range,Part#,RELEASE3!Quantityrange)
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
0
6/3/2005 1:56:02 AM
Thank you all very much. Everyone helped a lot. I appreciate it. 

Travis Dahlman
0
Dahlman (27)
6/3/2005 12:15:06 PM
Reply:

Similar Artilces:

Looking for a function that performs a special kind of Vlookup
Let say you have a table with columns A, B, C, D, E, F and you want to do a vlookup, but not the regular vlookup but one that takes into account values in columns A and B. For instance: A B C D E F Day Tue 2 4 3 3 Day Wed 7 6 8 5 Day Thu 2 1 9 7 Taking the 2nd row, I want to do a vlookup based on Day and Wed instead of just using Day. Is there a function like that or any combination of functions that I can use to do this? You can use sumproduct to do something close to what you want. It is similar to a sumif ...

Index vs VLookUp
Can somebody tell me the relative strength and weaknesses of INDEX v V/HLookUp -- Message posted from http://www.ExcelForum.com Hi just my 2 cents: - The VLOOKUP function may be a little bit faster than the combination of INDEX/MATCH - VLOOKUP requires that your lokkup value is in the leftmost column of your range. If this is not the case you have to switch to INDEX/MATCH -- Regards Frank Kabel Frankfurt, Germany > Can somebody tell me the relative strength and weaknesses of INDEX vs > V/HLookUp ? > > > --- > Message posted from http://www.ExcelForum.com/ See http://...

VLOOKUP and Multi Lists
Subject: VLOOKUP and Multi Lists need help with this dropdown multi list with vlookup. B1 is the dropdown list (on sheet named Data) that holds 4 lists (ad, el, mr, sz) this are lists that hold the codes for group names (TB =The Beatles BD= Bob Dylan..so on) each list has different number of rows. I have Colum A on the Main sheet linked to the Above drop down, so that if AD is selected in B1 then in A5 the dropdown list is AD and if B1 is MR then A5 drop down is changed to MR. this part works good. Now the problem, I want colum C to show the Group name depending on what colum A has ...

VLookup to merge Access Query data into Excel Report
Bernie, You mentioned to Bum that you had some code to merge Access Query Data into specific cells in Excel. Can you send me that code as well. I would greatly appreciate it. Vira-SJH ...

Vlookup Macro
Dear whoever can help.. Is it possible to create a Macro for a VLookUp that I use everyday or am I just being too damned lazy??? Ann Sure, why don't you give us some specifics. -- Don Guillett SalesAid Software donaldb@281.com "Ann" <anncshaw@yahoo.com> wrote in message news:09ac01c3b823$d1aedd10$a301280a@phx.gbl... > Dear whoever can help.. > > Is it possible to create a Macro for a VLookUp that I use > everyday or am I just being too damned lazy??? > > Ann Ann, Very simple. This code snippet creates a VLOOKUP that looks up the 3 value in table ...

Vlookups yielding wrong N/A results (numbers stored as texts and viceversa)
Hi there, my problem is that I have to do a number of vlookups (exact matches) on customer codes (which are numeric values). Most of the times, Excel returns an N/A error, even when it shouldn't, because the code is in both tables. My understanding is that this happens when Excel treats the code in the first table as a number, and the one in the second table as a text. How do I solve this extremely annoying problem? Since Excel is a spreadsheet and not a database, it allows you to choose a formatting, but not to set a data type for a field. So how do I make sure Excel treats the custome...

net 45 terms
I have a customer that requires net 45 terms reflected on the invoice. Money does not have net 45 listed in the drop down when I create the invoice. Does anyone know how to add a new set of terms to the list i.e 1% net 45. ...

If (Vlookup >0) working, but what if Vlookup cell does not exist
This formula works if the data is present, however, I'm getting #N/A if the lookup cell (A4) doesn't exist, which it may not on some days. =IF(VLOOKUP(A4,Sat!$A$16:$J$377,9,FALSE)>0,VLOOKUP(A4,Sat!$A$16:$J$377,9,FALSE),"") Can this formula be modified to also produce "" if the data representing A4 does not exist ? Thanks, Steve =IF(OR(ISNA(VLOOKUP(A4,Sat!$A$16:$J$377,9,FALSE)), VLOOKUP(A4,Sat!$A$16:$J$377,9,FALSE=0,"",VLOOKUP(A4,Sat!$A$16:$J$377,9,FALSE)) best wishes -- Bernard Liengme http://people.stfx.ca/bliengme Microsof...

=IF(ISERROR(VLOOKUP(A3,contacts!B:I,5,FALSE)), " ", VLOOKUP(A3,con
Hello, I'm using this vlookup, however it's returning a blank if the cell it's referencing is blank. how do I write this formula so the return of a blank cell is blank? =IF(ISERROR(VLOOKUP(A3,contacts!B:I,5,FALSE)), " ", VLOOKUP(A3,contacts!B:I,5,FALSE)) Thanks You lost us. You just told us the formula is doing exactly what you want ("return of a blank cell is blank"). You'll need to provide a clearer explanation of your problem. Examples are normally the best way. Regards, Fred "Jim" <Jim@discussions.microsoft.com&g...

help with vlookup
hi my vlookup forumla is =VLOOKUP(A17,'ORIGINAL GL'!$A$10:$E$187,4,FALSE) where column 4 contains data like HELLO*240203*ABC however when i do the vlookup only HELLO gets picked up. how do i do a vlookup such that the entire contents of the cell i colum 4 gets picked up. thank -- Message posted from http://www.ExcelForum.com I cannot duplicate your problem ! One wild, crazy question. Is your column wide enough to display the entire returned string of data ? -- Regards, RD -------------------------------------------------------------------- Please keep all corresponde...

Another HARD VLOOKUP Problem, can it be done ?
A hard problem with VLOOKUP ! Have serveral lines with Names/Adresses/Zip and a Cell with Codes of my Family in Excel 2003. I want to search it with VLOOKUP and one Cell of the found data must than be viewed in a Horizontal way. Example: VLOOKUP finds seven adresses with different codes T 3 T 2 G 4 E 7 W 4 The question is how do i get this data Horizontal like this : T 3 - T 2 - G 4 - E 7 - W 4 ( - = next Cell) How do i do that ? Is "=TRANSPOSE" an option and will it work with "VLOOKUP" ? Anybody ? > Example: VLOOKUP finds seven adresses...

Excel VBA-vlookup combo box problem
Hi can someone please help me. I have created a worksheet in which i have Vlookup formulaes reading o another worksheet which has combobox with drop down lists and linke cells. Everything works apart from the comboboxes whose populate linked cells are not being recognised by the v-lookup function. Th Vlookup is as follows: vlookup(e18,'policy'!c5:098,7,false) The data within the combo box is a mixture of text and numerical e. L32H11-1, EM2H11-1 I have put a double minus sign in front of e18 and it returns #value! Can someone please help me. thanking you in anticipation Saik ...

vlookup
I would like to define a named range = vlookup("2", range1,3,false). Whenever I try this, vlookup only returns the first match in range1, not the other match occurances. I have tried entering as an array formula but it only returns the first match. I admit I may be confused between ranges and arrays. Could someone please help? thanks... And I'm confused between ranges and values. Could you post a brief illustration of what your data might be and what result you want? Alan Beban odie wrote: > I would like to define a named range = vlookup("2", range1,3,false...

VLOOKUP Function #8
What is the error in the Range lookup ie. Entering True or False Tks John with true the lookup column has to be sorted since it will look for the first value where your lookup value is greater than or eqqual to the value in the column for false, an exact match must be made. and an error is returned if there is no match. sometimes the non match for cells which look alike is the result of format issues. "John H" wrote: > What is the error in the Range lookup > ie. Entering True or False > > Tks > John ...

VLOOKUP
Hello In my spreadsheet I have three columns; Task Activity Codes Description Each task have a corresponding activity code with its description. I am using VLOOKUP to populate corresponding description for the activity code numbers in the master worksheet. Here is an example; =VLOOKUP(C44,'Activity Codes'!$A$2:$B$126,2,0) Task Activity Code Description Sample Station WSC021 Repair Water Valve In some cases I have multiple activity codes for a single task like Task Activity Code Description Sample Station...

Vlookup to the left ?
I have a spreadsheet that looks like this: 1/1/09 <blank> 1/2/09 DISC 1/3/09 <blank> 1/4/09 <blank> 1/5/09 <blank> 1/6/09 DISC I need to search the table and be able to report out all the dates where the "DISC" value is in column B. The location where I want to report these will be a separate workbook in the spreadsheet and I want them each reported as a separate cell in a single row. Is there a way to use Vlookup to search the 2nd column and report the value from the first column....or is there some other m...

Vlookup #4
I am using the vlookup function to look up data over several columns. If there is nothing in one column, I would like for it to return a blank cell instead of the 0 it returns now. Is there something else I can put in the formula to do this. Any help will be appreciated. Thanks in advance. Todd, Best thing to do would be to post the formula that you're using. You could test to see if the vlookup returns a zero and change it to a blank or you may just want to set your page up to not display zeros. Tools/Options/View & uncheck "Zero Values" John "Todd" <...

Vlookup - Match
Hi, I need to know an explanation to each of the following formulas other than that they gave the same result: =AVERAGE(INDEX(SHEET1!I:J,MATCH($A$3,SHEET1!$B:$B,0)0)) =VLOOKUP($A$3,SHEET1!$B$14:$DO$83,18,FALSE) COLOMN k Represented in number 18 is the average of I:J Hi Khalil, Is the setup of your question correct? Perhaps it is, but - 18, counted from B gives column S, I think. - INDEX, I think, returns one single cell. So what's the need of AVERAGE around? - Your first function doesn't function ... at least: it give an error with me. Shouldn't it be: =INDEX(Sheet1!I:J...

VLOOKUP HELP 03-02-10
I need VLOOKUP to search across worksheets where the names are not the same (example) Worksheet1 (A): Doe, John; Worksheet2 (A): DoeJ; Worksheet3 (A) DoeJK. Can I have VLOOKUP find each name across worksheet 2 and 3? Here's what I have: =IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$A$206,1,FALSE)),"no","yes"). Thanks for any help. =vlookup() expects the table to be on a single sheet--not three different tables on 3 different sheets. But if you're just trying to determine what sheet that name is located on, you could use three formulas like: =isnumber(match(a2,...

Need help with VLookup
I have an Excel sheet that I am using as a source file for a mailing list. I enter First, Last, Street, City, State, and Zip. I would like to have State and Zip entered automatically IF (and only IF) the City I type is in the lookup list. The following works fine as long as the city is in the list however if the city is not in the list V lookup picks the nearest fit and enters it. This is not good. The formula does enter a blank instead of #N/A if no city is entered thus avoiding a string of #N/As in the columns. Is there a way to modify my formula so if the city is not in the V lookup list...

Can I combine an "if" statement in a vlookup formula?
I am trying to write a formula where the vlookup will find a cell and then use a if function to see if it is larger than a certain number then use a multiplier. Is this even possible? Thanks Jim Hi Jim, I think so, if I understand your question. =IF(VLOOKUP(D1,A1:B5,2,0)>C1,"Multiplier","Not greater") Where the number is in D1 and is compared to a number in C1. "Multiplier" & "Not greater" can be either values or cell references. HTH Regards, Howard "Jim Bringhurst" <JBringhurst@decore.com> wrote in message news:0de101c...

VLOOKUP 1st Friday in April and So On
Formula: =VLOOKUP(A2,DatesList!$A$2:$F$386,6,FALSE) returns #N/A. The Lookup Value = 1st Friday in April. The Table Array is the DateList in another spreadsheet. The Col_index_num is 6 and the Range_Lookup is set to "False". Both the Lookup Value column and Column 6 are formatted as TEXT. I used the Text to Columns feature to ensure both columns REALLY were formatted at Text. I have tried formatting both columns as General. I have used TRIM to remove leading and trailing spaces. I have used LENB to make sure the number of characters matched, and they do. I hav...

Money 2004 #45
Hello, I was wondering if there is any way to have the bill calendar show all the upcoming transactions instead of the next transaction. Older versions used to do that and made it easier to forcast account balances. Any advice is appreciated. Thanks. See http://www.bollar.org/msmoney/#Q34. "kwyoung" <anonymous@discussions.microsoft.com> wrote in message news:ab2001c3ebd2$ba6708c0$a601280a@phx.gbl... > Hello, I was wondering if there is any way to have the > bill calendar show all the upcoming transactions instead > of the next transaction. Older versions used t...

Vlookup #20
Is ther a way to add autocomplete to vlookup? Thanks for your help In what way? -- Regards, Peo Sjoblom "Jim O" <anonymous@discussions.microsoft.com> wrote in message news:358b01c42941$7f627af0$a301280a@phx.gbl... > Is ther a way to add autocomplete to vlookup? Thanks for > your help > When I key in the lookup field in the first field, I want it to autocomplete as it is keyed. Basically, I am keying the name of a drug and it would return the entire record of another worksheet showing the drug name and associated info. >-----Original Message----- >...

Vlookup #7
Hello, I have a list where I am doing a look up from another sheet based on an employees ID number. The 2nd spread sheet is a listing of all the employee activites they did that day expressed through different codes. My question is how do I bring back all the information if using a vlookup only brings back the first match and some employees have multiple activity codes for that day. Is there a function out there to solve this problem? Thank you, Denise The easiest thing that comes to my mind is using PIVOT TABLES. In a column drag employee ID, in data you can have your code cou...