Get 0 instead of #N/A in Formula

  • Thread starter Thread starter Sam
  • Start date Start date
S

Sam

I have the following formula in a cell:
=VLOOKUP(B33,'[AMG Portion Only - Mar 5 release.xls]
Sheet1'!$L$2:$AL$39,27,FALSE

If the condition is not met, I get #N/A which then messes
up the totaling. Is there any way I can modify this
formula so that a zero will be returned instead of the
#N/A so that I can get a total?
 
Hi
try
=IF(ISNA(VLOOKUP(B33,'[AMG Portion Only - Mar 5
release.xls]Sheet1'!$L$2:$AL$39,27,FALSE),0,VLOOKUP(B33,'[AMG Portion
Only - Mar 5 release.xls]Sheet1'!$L$2:$AL$39,27,FALSE))
 
Thank you, but i get an error message which highlights the
0 (after....False),0,)



-----Original Message-----
Hi
try
=IF(ISNA(VLOOKUP(B33,'[AMG Portion Only - Mar 5
release.xls]Sheet1'!$L$2:$AL$39,27,FALSE),0,VLOOKUP (B33,'[AMG Portion
Only - Mar 5 release.xls]Sheet1'!$L$2:$AL$39,27,FALSE))


--
Regards
Frank Kabel
Frankfurt, Germany
I have the following formula in a cell:
=VLOOKUP(B33,'[AMG Portion Only - Mar 5 release.xls]
Sheet1'!$L$2:$AL$39,27,FALSE

If the condition is not met, I get #N/A which then messes
up the totaling. Is there any way I can modify this
formula so that a zero will be returned instead of the
#N/A so that I can get a total?
Click to expand...

.
Click to expand...
 
Hi
forgot a parenthesis. Try
=IF(ISNA(VLOOKUP(B33,'[AMG Portion Only - Mar 5
release.xls]Sheet1'!$L$2:$AL$39,27,FALSE)),0,VLOOKUP(B33,'[AMG Portion
Only - Mar 5 release.xls]Sheet1'!$L$2:$AL$39,27,FALSE))

--
Regards
Frank Kabel
Frankfurt, Germany
Thank you, but i get an error message which highlights the
0 (after....False),0,)



-----Original Message-----
Hi
try
=IF(ISNA(VLOOKUP(B33,'[AMG Portion Only - Mar 5
release.xls]Sheet1'!$L$2:$AL$39,27,FALSE),0,VLOOKUP (B33,'[AMG
Portion Only - Mar 5 release.xls]Sheet1'!$L$2:$AL$39,27,FALSE))


--
Regards
Frank Kabel
Frankfurt, Germany
I have the following formula in a cell:
=VLOOKUP(B33,'[AMG Portion Only - Mar 5 release.xls]
Sheet1'!$L$2:$AL$39,27,FALSE

If the condition is not met, I get #N/A which then messes
up the totaling. Is there any way I can modify this
formula so that a zero will be returned instead of the
#N/A so that I can get a total?
Click to expand...

.
Click to expand...
Click to expand...
 
Sam

=IF(ISNA(VLOOKUP(B33,'[AMG Portion Only - Mar 5
release.xls]Sheet1'!$L$2:$AL$39,27,FALSE)),0,VLOOKUP(B33,'[AMG Portion
Only - Mar 5 release.xls]Sheet1'!$L$2:$AL$39,27,FALSE))

Note the second ")" ... FALSE)),0,VLOOKUP ...

Regards

Trevor


Sam said:
Thank you, but i get an error message which highlights the
0 (after....False),0,)



-----Original Message-----
Hi
try
=IF(ISNA(VLOOKUP(B33,'[AMG Portion Only - Mar 5
release.xls]Sheet1'!$L$2:$AL$39,27,FALSE),0,VLOOKUP (B33,'[AMG Portion
Only - Mar 5 release.xls]Sheet1'!$L$2:$AL$39,27,FALSE))


--
Regards
Frank Kabel
Frankfurt, Germany
I have the following formula in a cell:
=VLOOKUP(B33,'[AMG Portion Only - Mar 5 release.xls]
Sheet1'!$L$2:$AL$39,27,FALSE

If the condition is not met, I get #N/A which then messes
up the totaling. Is there any way I can modify this
formula so that a zero will be returned instead of the
#N/A so that I can get a total?
Click to expand...

.
Click to expand...
Click to expand...
 
An efficient way is:

Suppose that your current VLOOKUP formula is in E2...

In D2 enter:

=IF(ISNA(E2),0,D2)

Now you can sum the D-range.

You could also keep the #N/A's and use a SumIf formula to sum...

=SUMIF(Range,"<>#N/A")
 
Back
Top