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

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

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

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

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/