How to sum with formulas that are #n/a

[Help]

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

 

[Gary''s Student]

=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

 

[FinONE]

=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

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!

 

[Ken Wright]

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
------------------------------­------------------------------­----------------