Excel - Add up values from a column from multiple workbooks

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?
LVL 9
activematxAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
activematxAuthor Commented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
activematxAuthor Commented:
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
 
activematxAuthor Commented:
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
 
activematxAuthor Commented:
Is there a way, to make it say "0" instead of #N/A for the ones that don't work.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Did you see my last comment? That will suppress N/A and put in 0 instead.
0
 
Patrick MatthewsCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.