If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




How to sum a column with errors
I have a column where the cells contain a vlookup formula for an exact match
only. If there is no match, the value N/A# is returned. However, I would like to be able to sum the values that the vlookup does find. How do you write the formula so that instead of returning N/A# it returns zero or blank if the vlookup can't find a match? Thanks 
Ads 
#2




How to sum a column with errors
Best way
change the vlookup formula to =IF(ISNA(Vlookup_formula)),0,vlookup_formula) that way you will get 0 instead of #N/A otherwise =SUMIF(Range,"<>#N/A") will sum the non error values  Regards, Peo Sjoblom "blkane" > wrote in message ... >I have a column where the cells contain a vlookup formula for an exact >match > only. If there is no match, the value N/A# is returned. However, I would > like to be able to sum the values that the vlookup does find. > > How do you write the formula so that instead of returning N/A# it returns > zero or blank if the vlookup can't find a match? > > Thanks 
#3




How to sum a column with errors
Use the ISNA function inside of an IF Statement:
=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...)) HTH, Elkar "blkane" wrote: > I have a column where the cells contain a vlookup formula for an exact match > only. If there is no match, the value N/A# is returned. However, I would > like to be able to sum the values that the vlookup does find. > > How do you write the formula so that instead of returning N/A# it returns > zero or blank if the vlookup can't find a match? > > Thanks 
#4




How to sum a column with errors
Peo,
Thanks, I was reading some other threads at the same time you posted. I knew there had to be a way. I'm going to go with the ISNA function. This series of functions is new to me but I plan to use them more in the future. "Peo Sjoblom" wrote: > Best way > > change the vlookup formula to > > =IF(ISNA(Vlookup_formula)),0,vlookup_formula) > > that way you will get 0 instead of #N/A > > otherwise > > =SUMIF(Range,"<>#N/A") > > will sum the non error values > > >  > Regards, > > Peo Sjoblom > > > "blkane" > wrote in message > ... > >I have a column where the cells contain a vlookup formula for an exact > >match > > only. If there is no match, the value N/A# is returned. However, I would > > like to be able to sum the values that the vlookup does find. > > > > How do you write the formula so that instead of returning N/A# it returns > > zero or blank if the vlookup can't find a match? > > > > Thanks > > > 
#5




How to sum a column with errors
I tried that and I kept getting a formula area that would point directly to
the double "" in the formula. I finally figured out that I was missing a parens just before the ,"". You both have been very helpful. Thanks "Elkar" wrote: > Use the ISNA function inside of an IF Statement: > > =IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...)) > > HTH, > Elkar > > > "blkane" wrote: > > > I have a column where the cells contain a vlookup formula for an exact match > > only. If there is no match, the value N/A# is returned. However, I would > > like to be able to sum the values that the vlookup does find. > > > > How do you write the formula so that instead of returning N/A# it returns > > zero or blank if the vlookup can't find a match? > > > > Thanks 
#6




How to sum a column with errors
It's better to fix the #N/A errors but sometimes you may want to know if
there is an error. This will sum a range and ignore any #N/A errors: =SUMIF(A1:A10,"<>#N/A") Biff "blkane" > wrote in message ... >I have a column where the cells contain a vlookup formula for an exact >match > only. If there is no match, the value N/A# is returned. However, I would > like to be able to sum the values that the vlookup does find. > > How do you write the formula so that instead of returning N/A# it returns > zero or blank if the vlookup can't find a match? > > Thanks 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Sum some cells in column but not #n/a errors  Unicorn  Excel Discussion (Misc queries)  7  November 27th 06 04:37 PM 
Excel Throwing Circular Errors When No Errors Exist  MDW  Excel Worksheet Functions  1  August 10th 06 02:15 PM 
MAX value of a column that has #div/0 errors in it  Ray Elias  Excel Discussion (Misc queries)  2  April 24th 06 03:47 PM 
SUM a column with errors  Joe Gieder  Excel Worksheet Functions  3  February 24th 05 07:57 PM 
Summing a Column wit errors?  Jako  Excel Worksheet Functions  4  November 1st 04 07:32 PM 