Summing a Column with #N/A

[Lambright44]

I have a large dataset and am trying to sum the results from a VLookup. Unfortunately, the results bring in the #N/A value. I can paste special to do away with the value but then I have to do that everytime as I loose my formulae.
I know there was a way to make the N/A value into a Zero by using the Custom Formating but its ben so long that I have forgotten that step and I'm sure there is an easier way.
Have tried SumIf with mixed results.

 

[Joe4]

Welcome to the Board!

Just modify your VLOOKUP formulas to return 0 in such cases.
Here is the layout for those formulas:

In Excel 2007 and later:
=IFERROR(VLOOKUP(...),0)

Earlier versions of Excel:
=IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...))

 

[Aladin Akyurek]

Also...

=SUM(SUMIF(Range,{"<0",">0}))

 

[Joe4]

Aladin,
I think you are missing the double quote at the end of your equation...

 

[Aladin Akyurek]

Aladin,
I think you are missing the double quote at the end of your equation...

Yep...

=SUM(SUMIF(Range,{"<0",">0"}))

 

[barry houdini]

In Excel 2010 you can use this formula to sum a range and ignore errors

=AGGREGATE(9,6,range)