Solved

Vlookup and sumif

Posted on 2011-09-15
8
206 Views
Last Modified: 2012-05-12
I have two workbooks.
Workbook One is called Summary and is located at C:\Month. I have a tab labeled MS Aug. In MS Aug column A represents the account number. An example of an account number would be 4025RM28 (there are many more account numbers beginning with 4025.) In the same tab column G represents a balance in for each different 4025 account.
Workbook Two is called CD Spreadsheets. In CD Spreadsheets I have a tab labeled #20. In cell L5 I need to sum all the values from the Summary workbook, where the first four digits are “4025” from the Summary workbook from A3:A640 and place that sum in L5 in the tab labeled #20.
In other words, in the CD Spreadsheets at L5 go to the Summary workbook, tab MS Aug and add all the values in column G where the first four digits in column A = 4025 in the Summary workbook tab MS Aug.
0
Comment
Question by:Frank Freese
  • 5
  • 3
8 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 36544893
Hello fh freese

Normally this can be done with SUMIF function and a wildcard but if the source workbook might be closed SUMIF won't work so SUMPRODUCT might be the best bet...try this formula in L5

=SUMPRODUCT(LEFT('[Summary.xls]MS Aug'!A$3:A$640,4)="4025")+0,'[Summary.xls]MS Aug'!G$3:G$640)

regards, barry
0
 

Author Comment

by:Frank Freese
ID: 36544957
barry,
got an error at G$640
0
 

Author Comment

by:Frank Freese
ID: 36545027
no error now? but no values, yet - off to the dentist but check out tonight
0
 

Author Comment

by:Frank Freese
ID: 36545041
sorry barry, still getting error
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 36545159
Oh, apologies! I left out a parenthesis at the beginning - should be like this

=SUMPRODUCT((LEFT('[Summary.xls]MS Aug'!A$3:A$640,4)="4025")+0,'[Summary.xls]MS Aug'!G$3:G$640)

regards, barry
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 500 total points
ID: 36545330
Note: I assume initially that you will have both workbooks open - then the above formula should work. If you then close the Summary workbook the formula will change automatically to show the whole path for Summary .....and the result will still display.

regards, barry
0
 

Author Comment

by:Frank Freese
ID: 36546619
I think I got it!
0
 

Author Closing Comment

by:Frank Freese
ID: 36546620
thank you!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now