vlookup. HELP

C

Chris

I am trying to use vlookup to take a name from colum a on one work book and
look for numbers in another work book. I can do that fine. The problem is
that in the second work book there are many numbers for the same person and I
need these to be added together and a total of these numbers reported in
colum B in the first work book. Can anyone help
 
M

Max

Try SUMIF ..

Assuming source numbers to be added up are in Book2.xls in Sheet1's col B,
where the names are in col A

With Book2.xls simultaneously open,

In Book1.xls, in say, Sheet1,
assume the names are running in A1 down

Put in B1:
=SUMIF([Book2]Sheet1!$A:$A,A1,[Book2]Sheet1!$B:$B)
Copy B1 down
 
B

Bondi

I am trying to use vlookup to take a name from colum a on one work book and
look for numbers in another work book. I can do that fine. The problem is
that in the second work book there are many numbers for the same person and I
need these to be added together and a total of these numbers reported in
colum B in the first work book. Can anyone help
Click to expand...

Hi Chris,

Maybe you can use
Sumif()

Regards,
Bondi
 
S

shail

Hi Chris,

If I have understood your problem correctly

On the second work book write the formula at cell B2

=SUMIF(sheet1!A1:B10,B1,B1:B10)

B1 will have the name of the person.
This will sum-up the numbers for the person you are looking at the
first work book

I hope this helps you.


Thanks,

Shail
 
D

Dave Peterson

=sumif() sounds like a very good solution if the "sending" workbook is open.

But =sumif() won't work if you close that workbook.

You could use =sumproduct() though.

If you open both books, you can build the formula by typing and
pointing/selecting.

It should look something like:
=SUMPRODUCT(--(A1=[book3.xls]Sheet1!$A$1:$A$17),[book3.xls]Sheet1!$B$1:$B$17)

with the workbook open.

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
 
Top