It's best to keep it simple; don't put everything in one formula, do it step by step.
If possible, remove the spaces in filenames and sheet names.
Do the lookup tables really go down as far as row 65536? If not, use the used area as argument for your VLOOKUP.

I started on a new sheet, with the value to be looked up in A1.
In B1:
=VLOOKUP($A$1,[File1.xls]Sheet1!$B1:$J100,6,FALSE)
in C1:
=IF(ISNA(B1),0,B1)

And so down the columns for each file. Now you can sum C1:C12

It is not necessary to spell out all the filenames if you change the formula in B1 to:

=VLOOKUP($A$1,INDIRECT("[File"&ROW()&".xls]Sheet1!$B1:$J100"),6,FALSE)

Now you can copy the formula down to row 12 and the filenames will adjust automatically. The files need to be open in Excel,
though.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



"JW73" wrote in message ...
| I'm having some difficulty getting the nested formula below to work the way I
| want it to, compiling data from 5 separate files, for now - it'll be 12 at
| year end. All of the files are set up similarly - same columns are in each.
| I'm totalling up all column 6 data and dividing by all column 4 data to build
| a year to date percentage. It works great for references that have data in
| every monthly file - unfortunately, not all data reference points exist in
| all monthly files, so for months where the reference point doesn't exist,
| VLOOKUP returns a #N/A value. For some reason, SUM will not ignore this
| non-numeric value, so it returns a #N/A result as well. Is there a way to get
| the SUM formula (or possible another formula) to ignore the VLOOKUP #N/As so
| that I can build an percentage based on the data available? Or, would there
| be a way to get VLOOKUP to return a 0 result if it finds no data?
|
|
| =SUM(VLOOKUP(A1,'[File 1]Sheet 1'!$B$1:$J$65536,6,FALSE),(VLOOKUP(A1,'[File
| 2]Sheet 1'!$B$1:$J$65536,6,FALSE)),(VLOOKUP(A1,'[File 3]Sheet
| 1'!$B$1:$J$65536,6,FALSE)),(VLOOKUP(A1,'[File 4]Sheet
| 1'!$B$1:$J$65536,6,FALSE)),(VLOOKUP(A1,'[File 5]Sheet
| 1'!$B$1:$J$65536,6,FALSE)))/SUM(VLOOKUP(A1,'[File 1]Sheet
| 1'!$B$1:$J$65536,4,FALSE),(VLOOKUP(A1,'[File 2]Sheet
| 1'!$B$1:$J$65536,4,FALSE)),(VLOOKUP(A1,'[File 3]Sheet
| 1'!$B$1:$J$65536,4,FALSE)),(VLOOKUP(A1,'[File 4]Sheet
| 1'!$B$1:$J$65536,4,FALSE)),(VLOOKUP(A1,'[File 5]Sheet
| 1'!$B$1:$J$65536,4,FALSE)))