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