Solved

Excel - Add up values from a column from multiple workbooks

Posted on 2011-09-15
9
220 Views
Last Modified: 2012-06-27
Hi Excel Experts,

I have multiple Excel Workbooks.  (Example attached)   Jaggar---Bookstore.zip

Inside of these files you will see a sheet which looks like this:  Sheet 1
Each file has different numbers (in different order).  Here is another workbook screenshot: Sheet 2
What I need to do is add up all the numbers from the sheet into a single data set.  (Only the yellow highlighted part).  [Don't pay attention to the other sheets, in the workbook.  We are only focusing on the yellow highlighted part).  (I know I could use a pivot table, and copy all of the data into a single sheet; but I would prefer to do this using a formula).

Can someone help?
0
Comment
Question by:activematx
  • 4
  • 4
9 Comments
 
LVL 50

Expert Comment

by:teylyn
ID: 36546665
Hello,

without opening the attached files: You can use Vlookup on closed files to pull values.

In a workbook with PLU numbers in column A, use something like

=vlookup(A2,[FirstBook.xls]Sheet1!$E$1:$F$1000,2,False)+vlookup(A2,[SecondBook.xls]Sheet1!$E$1:$F$1000,2,False)+vlookup(A2,[ThirdBook.xls]Sheet1!$E$1:$F$1000,2,False)

The easiest way to create the vlookups is to have all files open. Start typing the Vlookup formula and click into the external workbook to select the lookup range.

When you've entered the formula, you can close the external workbooks and Excel will adjust the file path.

cheers, teylyn
0
 
LVL 9

Author Comment

by:activematx
ID: 36546706
Hi Teylyn,

I tried doing it.  I put into a new workbook this formula:

=VLOOKUP(A1,'[Zone 1 - Jag Bookstore.xlsx]Variance'!$E$3:$F$153,2,FALSE)+VLOOKUP(A1,'[Zone 2 - Jag Bookstore.xlsx]Variance'!$E$3:$F$74,2,FALSE)+VLOOKUP(A1,'[Zone 3 - Jag Bookstore.xlsx]Variance'!$E$3:$F$68,2,FALSE)+VLOOKUP(A1,'[Zone 4 - Jag Bookstore.xlsx]Variance'!$E$3:$F$125,2,FALSE)+VLOOKUP(A1,'[Zone 5 - Jag Bookstore.xlsx]Variance'!$E$3:$F$127,2,FALSE)

Open in new window


However, I get a #N/A when I do that formula.
0
 
LVL 50

Expert Comment

by:teylyn
ID: 36546741
If vlookup returns N/A that means the search term is not found. Can you confirm that the value in A1 is actually present in the respective ranges of the workbooks?

Look out for leading/trailing blanks, numbers stored as text, and the like.
0
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
ID: 36546748
Then, of course, if a PLU is not present in ALL five workbooks, you need to trap errors, for example like this

=IFERROR(VLOOKUP(A2,'[Zone 1 - Jag Bookstore.xlsx]Variance'!$E$1:$F$153,2,FALSE),0)
+IFERROR(VLOOKUP(A2,'[Zone 2 - Jag Bookstore.xlsx]Variance'!$E$2:$F$74,2,FALSE),0)
+IFERROR(VLOOKUP(A2,'[Zone 3 - Jag Bookstore.xlsx]Variance'!$E$2:$F$68,2,FALSE),0)
+IFERROR(VLOOKUP(A2,'[Zone 4 - Jag Bookstore.xlsx]Variance'!$E$2:$F$125,2,FALSE),0)

etc.

cheers, teylyn
0
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

 
LVL 9

Author Comment

by:activematx
ID: 36546769
Looks like it is working.  Is there a way, to make it say "0" instead of #N/A for the ones that don't work.
0
 
LVL 9

Author Comment

by:activematx
ID: 36546774
This is what I am using:

=VLOOKUP(A3,'HNHADocs:YEAR END AUDIT:FY11 Closeout/Audit:Inventory Related:HAVO INVENTORY:Jaggar - Bookstore:[Zone 1 - Jag Bookstore.xlsx]Variance'!$E$3:$F$153,2,FALSE)
0
 
LVL 9

Author Comment

by:activematx
ID: 36546802
Is there a way, to make it say "0" instead of #N/A for the ones that don't work.
0
 
LVL 50

Expert Comment

by:teylyn
ID: 36546825
Did you see my last comment? That will suppress N/A and put in 0 instead.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36546838
activematx,

No points for this, please, as teylyn has answered your question.

For more information about VLOOKUP, including how to return an alternate answer instead of #N/A, please see:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_2637-Six-Reasons-Why-Your-VLOOKUP-or-HLOOKUP-Formula-Does-Not-Work.html

The article has voting buttons at the top; if you like it, then please vote yes :)

Patrick
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

707 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

14 Experts available now in Live!

Get 1:1 Help Now