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:
Each file has different numbers (in different order).  Here is another workbook screenshot:
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
Who is Participating?

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

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

Author 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)
``````

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

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

Author 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

Author 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

Author Commented:
Is there a way, to make it say "0" instead of #N/A for the ones that don't work.
0

Microsoft MVP ExcelCommented:
Did you see my last comment? That will suppress N/A and put in 0 instead.
0

Commented:
activematx,

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

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.