Ignoring #N/A in an Autosum range

J

Jaye

I need to be able to add data in a column, and some of the cells in that
column have an #N/A in it, resulting from a VLOOKUP. How do I add the data in
the column, and have it ignore the #N/A?
 
R

Ron Coderre

Try something like this:

For values in A1:A10

B1: =SUMIF(A1:A10,"<>#N/A",A1:A10)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
J

Jaye

Awesome - worked perfectly!! Thanks so much.

Another question - if I do a VLOOKUP and the resulting answer is the #N/A
again, how can I get the result to be a zero (0)?
 
B

Biff

See your other post.

Biff

Jaye said:
Awesome - worked perfectly!! Thanks so much.

Another question - if I do a VLOOKUP and the resulting answer is the #N/A
again, how can I get the result to be a zero (0)?
Click to expand...
 
C

charles44

I have the same concern so instead of creating a new thread, I thought i'd
highjack this one. I have:

=IF(ISERROR(VLOOKUP(D13,$B$2:$C$12,2,FALSE)),"0",VLOOKUP(D13,$B$2:$C$12,2,FALSE))

to show a #n/a as a "0" (which is what I want), but there must be a better
way to do this. Is there?

If so or not, how would I apply them to multiple cells (column edit) with
different references (other then the "D13", also say, "D14",etc.) in a single
edit?

Thanks, Charles
 
B

Bob Phillips

=SUM(IF(NOT(ISERROR(E1:E4)),E1:E4))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
G

Gord Dibben

Select the range of cells and run this macro.

Sub ErrorTrapAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(ISERROR*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(ISERROR(" & myStr & "),0," & myStr & ")"
End If
End If
Next
End Sub

I personally would use ISNA rather than ISERROR in a Lookup formula so' not to
mask other errors.


Gord Dibben MS Excel MVP
 
Top