SUM with nested VLOOKUPS - can i make it ignore N/As?

J

JW73

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

Niek Otten

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



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

Gary Brown

I've heard that 2007 has a shorter function but for 2003 and lower, you need
to do something like...

if(isna(VLOOKUP(A1,'[File 2]Sheet
1'!$B$1:$J$65536,6,FALSE)),0,VLOOKUP(A1,'[File 2]Sheet
1'!$B$1:$J$65536,6,FALSE))

FYI, you can change $B$1:$J$65536 to $B:$J.

--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown
 
N

Niek Otten

$B1:$J100 should have been $B$1:$J$100 in both formulas.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

JW73

Thanks for the tips, I'll test them out later this evening - I've never
worked with the IFNA function before, sounds like that will clear it up.. on
the machine I've got the system set up on, I'm using $B:$J for the range, my
excel2007 at home reformatted it as $J$65535 - I'll be using the system on a
mix of 2003 and 2007 systems, so it has to be compatible with the older
format.
 
P

Peo Sjoblom

It's ISNA not IFNA however
for good spreadsheet design and much speedier
result you should go with Niek's solution using multiple
smaller formulas and a summary formula. That way it
will be much easier to audit.

--


Regards,


Peo Sjoblom

JW73 said:
Thanks for the tips, I'll test them out later this evening - I've never
worked with the IFNA function before, sounds like that will clear it up..
on
the machine I've got the system set up on, I'm using $B:$J for the range,
my
excel2007 at home reformatted it as $J$65535 - I'll be using the system on
a
mix of 2003 and 2007 systems, so it has to be compatible with the older
format.

JW73 said:
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)))
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Nested IF Functions with OFFSET 3
Vlookups to return a N/A value 3
Vlook-up in report with column Expansion 5
IF and VLOOKUP functions for a matrix overview 4
VLOOKUP 4
Excel Trying to do a vlookup with a nested right function on the array 3
Vlookup and If statement help 1
isna vlookup #n/a but not false 1

Top