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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,329
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 12, 2006
Messages
44,061
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
Joined
Mar 2, 2006
Messages
548
Sum if should work but you could also use sumproduct
Book1
ABCDE
18Bart10Bart30
19Lisa10Homer10
20Bart20Lisa15
21Homer10
22Lisa5
Sheet1
 

noren

New Member
Joined
May 20, 2007
Messages
15

ADVERTISEMENT

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
Joined
Mar 2, 2006
Messages
548
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
Joined
May 20, 2007
Messages
15

ADVERTISEMENT

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
Joined
Oct 12, 2006
Messages
44,061
Code:
=SUMPRODUCT(--([file1.xls]Sheet1!$A$4:$A$100=B1),[file1.xls]Sheet1!$C$4:$C$100)
 

Milo_Minderbinder

Well-known Member
Joined
Mar 2, 2006
Messages
548
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"
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,160
Messages
5,768,537
Members
425,480
Latest member
br400821

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
Top