VLOOKUP and SUMIF

[noren]

Hi,

I have 2 files. 1 File accesses information from the other using VLOOKUP. I am trying to find a formula which will do what the VLOOKUP does BUT without stopping at the first instance of the condition.
More specifically, Im trying to sum up numbers that match the critiria from the whole worksheet (1 tab).


example:
file1:

bart 10
lisa 10
bart 20
homer 10
lisa 5

file2:

bart 30
lisa 15
homer 10

In one file this would be a simple SUMIF, but when I need to access the other file using VLOOKUP, it sometimes returns the very annoying "#N/A", and then the result will always be "#N/A"

Any and all help is appriciated!

Thanks,
Oren

 

[Norie]

Oren

Why can't you just use SUMIF?

I can't see a problem with the data being in 2 different workbook/sheets.

 

[Jonmo1]

I don't understand why SUMIF Doesn't work for this?

=SUMIF([file1.xls]Sheet1!$A:$A,A1,[file1.xls]Sheet1!$B:$B)

this will sum file1 Column B WHERE file1 Column A = file2 A1

 

[Milo_Minderbinder]

Sum if should work but you could also use sumproduct

Book1
	A	B	C	D	E
18	Bart	10		Bart	30
19	Lisa	10		Homer	10
20	Bart	20		Lisa	15
21	Homer	10
22	Lisa	5
Sheet1

 

[noren]

Thanks for the quick replies...

Both are fine and will work if both files are open. The problem is that if file1 is closed after its updated, file2 will show all "#N/A" until file1 is open. This is why I need to use VLOOKUP.

Thanks,
Oren

 

[Milo_Minderbinder]

Thanks for the quick replies...

Both are fine and will work if both files are open. The problem is that if file1 is closed after its updated, file2 will show all "#N/A" until file1 is open. This is why I need to use VLOOKUP.

Thanks,
Oren

Sumproduct will work on closed files.

 

[noren]

Thanks agin but I still need some clarifications.

I dont understand the formula you have with the *1.

How would I write a formula for the follwing:


file1

The names range from A4:A100
The numbers range from C4:C100

Bart 10
lisa 5
homer 3
bart 3
lisa 3


file2
Cell B1 = bart
B2 = (formula)



Thanks

 

[Jonmo1]

=SUMPRODUCT(--([file1.xls]Sheet1!$A$4:$A$100=B1),[file1.xls]Sheet1!$C$4:$C$100)

 

[Milo_Minderbinder]

I don't normally use the "--' that jon uses (I guess I should). multipling by one is required in my formula b/c the formula is evaluating a "product"

 

[noren]

Thanks! Works perfectly!