Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
use Vlookup for sum all found value?
Hi,
I am new here and have a problem hope I can get help in here! I have two worksheet "W1" and "W2" In W1 worksheet, it has a column "emp_pin" which is unique. This value is used in the Vlookup function to find a numeric value "Paid" in W2 worksheet. If "W1" and "W2" are one to one relationship, it will work fine. However I have encountered the one to many relationship, it means one row in "W1" has many rows in "W2". I just wonder if I can sum up all the found value "paid" in "W2". should I still use Vlookup or there is something out there I can use? Thanks... Ivna |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
use Vlookup for sum all found value?
say sheet1.a1 = "Blah"
sheet2.column a & b = blah 5 eggs 7 yo 9 blah 3 fish 5 drink 7 the formula would be =sum(if(sheet2!a1:a6=sheet1!a1, sheet2!b1:b7,0)) after you've type this formula press cntrl + shft + enter at the sam time. if you don't the formula will be an error. the final formula should look like {=sum(if(sheet2!a1:a6=sheet1!a1, sheet2!b1:b7,0))} Cheers, B -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
use Vlookup for sum all found value?
Hi Ivan
If your task is no more complex, you can use the sumif function, the on line help is useful in this case. The array's formulas are little bit more complex and need a some knoweledge about this. Regards Fernando Ortiz "Ivan" escribió en el mensaje ... Hi, I am new here and have a problem hope I can get help in here! I have two worksheet "W1" and "W2" In W1 worksheet, it has a column "emp_pin" which is unique. This value is used in the Vlookup function to find a numeric value "Paid" in W2 worksheet. If "W1" and "W2" are one to one relationship, it will work fine. However I have encountered the one to many relationship, it means one row in "W1" has many rows in "W2". I just wonder if I can sum up all the found value "paid" in "W2". should I still use Vlookup or there is something out there I can use? Thanks... Ivna |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
use Vlookup for sum all found value?
Array enter =SUM(IF(A1:A7="blah",B1:B7))
Alan Beban Ivan wrote: Hi, I am new here and have a problem hope I can get help in here! I have two worksheet "W1" and "W2" In W1 worksheet, it has a column "emp_pin" which is unique. This value is used in the Vlookup function to find a numeric value "Paid" in W2 worksheet. If "W1" and "W2" are one to one relationship, it will work fine. However I have encountered the one to many relationship, it means one row in "W1" has many rows in "W2". I just wonder if I can sum up all the found value "paid" in "W2". should I still use Vlookup or there is something out there I can use? Thanks... Ivna |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
use Vlookup for sum all found value?
thanks for all of you ....
I think it will solve my problem. "bcmiller " wrote in message ... say sheet1.a1 = "Blah" sheet2.column a & b = blah 5 eggs 7 yo 9 blah 3 fish 5 drink 7 the formula would be =sum(if(sheet2!a1:a6=sheet1!a1, sheet2!b1:b7,0)) after you've type this formula press cntrl + shft + enter at the same time. if you don't the formula will be an error. the final formula should look like {=sum(if(sheet2!a1:a6=sheet1!a1, sheet2!b1:b7,0))} Cheers, BC --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP would begin one row below what the first one found? | Excel Worksheet Functions | |||
Formula have a VLOOKUP but want it to =0 if no value is found | Excel Worksheet Functions | |||
VLookup Value not found ? | Excel Discussion (Misc queries) | |||
Vlookup, return zero if not found | Excel Worksheet Functions | |||
vlookup not found | Excel Worksheet Functions |