Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add multiple vlookup results
I have a spreadsheet that contains names of groups and numerical results for
them as below. Column A Column B Frame .50 Build .25 Assy 1 .33 Build 1.5 I need to write a formula that will look for all the occurances of "Build" and add the numerical amounts together. I've been trying to do it with vlookup, but maybe that's not the best choice. Does anyone know the best way to do this? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add multiple vlookup results
Hi Dawn
try this =SUMPRODUCT(--(A2:A5="Build"),(B2:B5)) HTH Regards from Brazil Marcelo "Dawn" escreveu: I have a spreadsheet that contains names of groups and numerical results for them as below. Column A Column B Frame .50 Build .25 Assy 1 .33 Build 1.5 I need to write a formula that will look for all the occurances of "Build" and add the numerical amounts together. I've been trying to do it with vlookup, but maybe that's not the best choice. Does anyone know the best way to do this? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add multiple vlookup results
Try =Sumif(A2:A10,"Build",B2:B10) adjust ranges to suit. -- Vito ------------------------------------------------------------------------ Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182 View this thread: http://www.excelforum.com/showthread...hreadid=553761 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add multiple vlookup results
Nel post
*Dawn* ha scritto: I have a spreadsheet that contains names of groups and numerical results for them as below. Column A Column B Frame .50 Build .25 Assy 1 .33 Build 1.5 I need to write a formula that will look for all the occurances of "Build" and add the numerical amounts together. I've been trying to do it with vlookup, but maybe that's not the best choice. Does anyone know the best way to do this? Thanks! I think th best way should by the function SUMIF: =SUMIF(A2:A4,"Build",B2:B4) -- Ciao Franz Verga from Italy |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add multiple vlookup results
Thanks to all of you! However, I don't think I explained it very well. I
actually have two spreadsheets; the first as detailed below, and the second one looks like this. Column A Column B Frame XXX Assy 1 XXX Build XXX Assy 2 XXX Etc In the second worksheet, I need to create a formula that will look for each item in Column A in the first worksheet and populate Column B with the amount listed next to the matched Department. Vlookup works very well for all except the Build one because it has multiple entries in the first worksheet that need to be added together. I'm sorry I didn't explain this better the first time. I just figured you all knew what was in my head!! :) Thanks!! "Franz Verga" wrote: Nel post *Dawn* ha scritto: I have a spreadsheet that contains names of groups and numerical results for them as below. Column A Column B Frame .50 Build .25 Assy 1 .33 Build 1.5 I need to write a formula that will look for all the occurances of "Build" and add the numerical amounts together. I've been trying to do it with vlookup, but maybe that's not the best choice. Does anyone know the best way to do this? Thanks! I think th best way should by the function SUMIF: =SUMIF(A2:A4,"Build",B2:B4) -- Ciao Franz Verga from Italy |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add multiple vlookup results
OK Dawn,
Assuming your data is on the WS 2 A2:B1000 on the WS1 use the formula =sumproduct(--(ws2!$A$2:$A$1000=A2),(WS2!$B$2:$B$1000)) hope this helps Regards from Brazil Marcelo "Dawn" escreveu: Thanks to all of you! However, I don't think I explained it very well. I actually have two spreadsheets; the first as detailed below, and the second one looks like this. Column A Column B Frame XXX Assy 1 XXX Build XXX Assy 2 XXX Etc In the second worksheet, I need to create a formula that will look for each item in Column A in the first worksheet and populate Column B with the amount listed next to the matched Department. Vlookup works very well for all except the Build one because it has multiple entries in the first worksheet that need to be added together. I'm sorry I didn't explain this better the first time. I just figured you all knew what was in my head!! :) Thanks!! "Franz Verga" wrote: Nel post *Dawn* ha scritto: I have a spreadsheet that contains names of groups and numerical results for them as below. Column A Column B Frame .50 Build .25 Assy 1 .33 Build 1.5 I need to write a formula that will look for all the occurances of "Build" and add the numerical amounts together. I've been trying to do it with vlookup, but maybe that's not the best choice. Does anyone know the best way to do this? Thanks! I think th best way should by the function SUMIF: =SUMIF(A2:A4,"Build",B2:B4) -- Ciao Franz Verga from Italy |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add multiple vlookup results
Nel post
*Dawn* ha scritto: Thanks to all of you! However, I don't think I explained it very well. I actually have two spreadsheets; the first as detailed below, and the second one looks like this. Column A Column B Frame XXX Assy 1 XXX Build XXX Assy 2 XXX Etc In the second worksheet, I need to create a formula that will look for each item in Column A in the first worksheet and populate Column B with the amount listed next to the matched Department. Vlookup works very well for all except the Build one because it has multiple entries in the first worksheet that need to be added together. I'm sorry I didn't explain this better the first time. I just figured you all knew what was in my head!! :) Thanks!! You have just to modify the formula; put this formula in B2 of your second sheet and fill down: =SUMIF(Sheet1!A2:A4,A2,Sheet1!B2:B4) -- Ciao Franz Verga from Italy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP not returning results | Excel Worksheet Functions | |||
Calculate multiple results from multiple input values? | Excel Discussion (Misc queries) | |||
LOOKUP multiple results but ignore duplicates. | Excel Worksheet Functions | |||
Multiple vlookup | Excel Discussion (Misc queries) | |||
format cell based on results of vlookup function | Excel Worksheet Functions |