# VLOOKUP and SUMIF

#### noren

##### New Member
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

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### Norie

##### Well-known Member
Oren

Why can't you just use SUMIF?

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

#### Jonmo1

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

Code:
``=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

##### Well-known Member
Sum if should work but you could also use sumproduct
Book1
ABCDE
18Bart10Bart30
19Lisa10Homer10
20Bart20Lisa15
21Homer10
22Lisa5
Sheet1

#### noren

##### New Member
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

##### Well-known Member
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

##### New Member
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

##### MrExcel MVP
Code:
``=SUMPRODUCT(--([file1.xls]Sheet1!\$A\$4:\$A\$100=B1),[file1.xls]Sheet1!\$C\$4:\$C\$100)``

#### Milo_Minderbinder

##### Well-known Member
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

##### New Member
Thanks! Works perfectly!

Replies
1
Views
163
Replies
3
Views
186
Replies
15
Views
792
Replies
6
Views
449
Replies
0
Views
622

### Forum statistics

1,186,160
Messages
5,956,272
Members
438,245
Latest member
bhavdip_mangukiya

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back