VLOOKUP and SUMIF

noren

New Member
Joined
May 20, 2007
Messages
15
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Oren

Why can't you just use SUMIF?

I can't see a problem with the data being in 2 different workbook/sheets.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Code:
=SUMPRODUCT(--([file1.xls]Sheet1!$A$4:$A$100=B1),[file1.xls]Sheet1!$C$4:$C$100)
 
Upvote 0
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"
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top