How to sum with formulas that are #n/a

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using vlookup and I am getting an error (#N/A) because there is no link
on the worksheet that I am grabing the information from. How do I keep the
(#N/A) and still SUM the information in that column without getting an error
(#N/A)?
 
=SUM(IF(ISERROR(A1:A18),0,A1:A18))

This is an array formula entered with CNTRL-SHFT-ENTER rather than just ENTER.

It sums A1 thru A18 and it ignores any error cells in that range
 
=SUM(IF(ISERROR(A1:A18),0,A1:A18))

This is an array formula entered with CNTRL-SHFT-ENTER rather than just ENTER.

It sums A1 thru A18 and it ignores any error cells in that range
Click to expand...

This is absolutely amazing (the Ctrl-Shft-Enter function). Although
I'm still lost with regards to array formula but it's definitely worth
learning (especially after I managed to get the SUM with the #N/A
results correctly!).

Thanks a mill!
 
Assume you must have some reason for wanting the NA, else you may want to
consider trapping the error at source rather than catering for the impact it
has on your formulas:-

General use =IF(ISNA(Your_Formula),0,Your_Formula)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------­------------------------------­----------------
 
Back
Top